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), DocumentCategor y
from vDocumentCatego ry
where DocumentType = 'I'
and CreatedDate between '1/1/2006' and '12/1/2006'
and DocumentStatusI d = 1
group by DocumentCategor y

Thanks
Andy

[Marc Gravell]

Re: Convert sql to linq?

Well, hard to tell without reproducable code, but it is going to be
something like:

var qry = from category in ctx.DocumentCat egories
where category.Docume ntType == "I"
&& category.Create dDate >= start
&& category.Create dDate <= end
&& category.Docume ntStatusId == 1
group category by category.Catego ry into grp
select new
{
Category = grp.Key,
SubTotal = grp.Sum(x =x.SubTotal)
};

[Pavel Minaev]

Re: Convert sql to linq?

"Andy" <andyj@med-associates.comw rote in message
news:41dd6c9f-4835-46cd-9f32-e573fb7c7494@x4 1g2000hsb.googl egroups.com...

Hi,
>
I have a sql statement I'd like to convert to Linq to Sql. Here's the
sql:
>
select sum( SubTotal), DocumentCategor y
from vDocumentCatego ry
where DocumentType = 'I'
and CreatedDate between '1/1/2006' and '12/1/2006'
and DocumentStatusI d = 1
group by DocumentCategor y

from dc in vDocumentCatego ry
where dc.DocumentType == 'I' && dc.CreatedDate >= new DateTime(2006, 1, 1)
&& dc.CreatedDate <= new DateTime(2006, 12, 1) && dc.DocumentStat usId == 1
group SubTotal by dc.DocumentCate gory into g
select new { DocumentCategor y = g.Key, Total = g.Sum() }

[Andy]

Re: Convert sql to linq?

That works, thanks!

On Sep 2, 9:52 am, Marc Gravell <marc.grav...@g mail.comwrote:

Well, hard to tell without reproducable code, but it is going to be
something like:
>
        var qry = from category in ctx.DocumentCat egories
                      where category.Docume ntType == "I"
                        && category.Create dDate >= start
                        && category.Create dDate <= end
                        && category.Docume ntStatusId == 1
                      group category by category.Catego ry into grp
                      select new
                      {
                          Category = grp.Key,
                          SubTotal = grp.Sum(x =x.SubTotal)
                      };

[Marc Gravell]

Re: Convert sql to linq?

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

Re: Convert sql to linq?

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...@g mail.comwrote:

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