Convert sql to linq?

[Andy]

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

[Marc Gravell]

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)
};

[Pavel Minaev]

"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() }

[Andy]

That works, thanks!

On Sep 2, 9:52*am, Marc Gravell <[e-mail]:

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)
* * * * * * * * * * * };

[Marc Gravell]

[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

[Andy]

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 <[e-mail]:

Watch for i18n - I have no idea whether the OP meant December 1st or
January 12th (hence I cheated and said neither...)