469,358 Members | 1,605 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,358 developers. It's quick & easy.

Convert sql to linq?

Hi,

I have a sql statement I'd like to convert to Linq to Sql. Here's the
sql:

select sum( SubTotal), DocumentCategory
from vDocumentCategory
where DocumentType = 'I'
and CreatedDate between '1/1/2006' and '12/1/2006'
and DocumentStatusId = 1
group by DocumentCategory

Thanks
Andy
Sep 2 '08 #1
5 6324
Well, hard to tell without reproducable code, but it is going to be
something like:

var qry = from category in ctx.DocumentCategories
where category.DocumentType == "I"
&& category.CreatedDate >= start
&& category.CreatedDate <= end
&& category.DocumentStatusId == 1
group category by category.Category into grp
select new
{
Category = grp.Key,
SubTotal = grp.Sum(x =x.SubTotal)
};
Sep 2 '08 #2
"Andy" <an***@med-associates.comwrote in message
news:41**********************************@x41g2000 hsb.googlegroups.com...
Hi,

I have a sql statement I'd like to convert to Linq to Sql. Here's the
sql:

select sum( SubTotal), DocumentCategory
from vDocumentCategory
where DocumentType = 'I'
and CreatedDate between '1/1/2006' and '12/1/2006'
and DocumentStatusId = 1
group by DocumentCategory
from dc in vDocumentCategory
where dc.DocumentType == 'I' && dc.CreatedDate >= new DateTime(2006, 1, 1)
&& dc.CreatedDate <= new DateTime(2006, 12, 1) && dc.DocumentStatusId == 1
group SubTotal by dc.DocumentCategory into g
select new { DocumentCategory = g.Key, Total = g.Sum() }

Sep 2 '08 #3
That works, thanks!

On Sep 2, 9:52*am, Marc Gravell <marc.grav...@gmail.comwrote:
Well, hard to tell without reproducable code, but it is going to be
something like:

* * * * var qry = from category in ctx.DocumentCategories
* * * * * * * * * * * where category.DocumentType == "I"
* * * * * * * * * * * * && category.CreatedDate >= start
* * * * * * * * * * * * && category.CreatedDate <= end
* * * * * * * * * * * * && category.DocumentStatusId == 1
* * * * * * * * * * * group category by category.Category into grp
* * * * * * * * * * * select new
* * * * * * * * * * * {
* * * * * * * * * * * * * Category = grp.Key,
* * * * * * * * * * * * * SubTotal = grp.Sum(x =x.SubTotal)
* * * * * * * * * * * };
Sep 2 '08 #4
[Pavel replied...]
Total = g.Sum()
You'd need to select the specific property to sum, otherwise you are
summing classes
DateTime(2006, 12, 1)
Watch for i18n - I have no idea whether the OP meant December 1st or
January 12th (hence I cheated and said neither...)

Marc
Sep 2 '08 #5
heh.. the date was just an example anyway. The real query has
variables in place of the hard code values I specified.

On Sep 2, 10:45*am, Marc Gravell <marc.grav...@gmail.comwrote:
Watch for i18n - I have no idea whether the OP meant December 1st or
January 12th (hence I cheated and said neither...)
Sep 3 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Amil Hanish | last post: by
9 posts views Thread by =?Utf-8?B?cmF1bGF2aQ==?= | last post: by
reply views Thread by =?Utf-8?B?SHlwZXJjb2Rlcg==?= | last post: by
4 posts views Thread by =?Utf-8?B?RXJpYyBGYWxza2Vu?= | last post: by
14 posts views Thread by thj | last post: by
3 posts views Thread by =?Utf-8?B?UGF1bCBQcmV3ZXR0?= | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.