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]

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!

 

[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.