473,566 Members | 3,112 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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), 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
Sep 2 '08 #1
5 6503
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)
};
Sep 2 '08 #2
"Andy" <an***@med-associates.comw rote in message
news:41******** *************** ***********@x41 g2000hsb.google groups.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() }

Sep 2 '08 #3
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)
* * * * * * * * * * * };
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...@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...)
Sep 3 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1111
by: Amil Hanish | last post by:
I had a VS 2005 web app (not using Linq yet). I migrated to VS 2008 and the app still worked fine. Now I want to try out Linq. I modified the web.config to load the Linq DLLs and added "using System.Linq". I also modified the properties Build to target .NET 3.5. Writing code works fine, but... When I compile, no Linq statements are...
9
2493
by: =?Utf-8?B?cmF1bGF2aQ==?= | last post by:
Hi all: after reading different places/sites about linq... I ran into these questions: 1. What framework do we need to run linq ? (does it depend on what version of visual studio we have?) how about vs2008? is it different name space or framework for linq xml or linq sql? ( 2. do we need to have references to what linq's dlls. or...
0
2353
by: =?Utf-8?B?SHlwZXJjb2Rlcg==?= | last post by:
I'm encountering some strange behavior after deploying a ASP.net 3.5 website to production, i'm unable to reproduce these in my dev environment. This error seems to occur very randomly but it's occuring enough to be a real cause for concern. The errors occur a couple times a week and the website is hit with constant traffic 24x7. Below are the...
4
2175
by: =?Utf-8?B?RXJpYyBGYWxza2Vu?= | last post by:
We’re storing our main entity in an insert only table which stores the history of past revisions, but we’re facing problems with storing this history as LINQ will only update the entity, and not reinsert it with a different revision number. Compounding the issue, we’ve also got an associated table storing properties for our entities...
14
3665
by: thj | last post by:
Hi, I was wondering what you guys are using and why? LINQ to SQL or NHibernate? Thanks in advance, Tommy
1
2628
by: Cezus | last post by:
Hello, I cannot convert the following query in the dataset to a string. It says it cannot get more then 2034 chars long... the string just ends at 2034 characters... this is where it goes wrong in the code: string xml = Convert.ToString(mijnQTA.GetDataXMLForAuto());
3
12421
by: =?Utf-8?B?UGF1bCBQcmV3ZXR0?= | last post by:
I'm attempting to use LINQ to insert a record into a child table and I'm receiving a "Specified cast is not valid" error that has something to do w/ the keys involved. The stack trace is: ====================== Message: Specified cast is not valid. Type: System.InvalidCastException Source: System.Data.Linq TargetSite: Boolean...
1
8668
by: alex21 | last post by:
Ok i am trying to use a Linq query to access a dictionary. public static Dictionary<string, Client> Clients = new Dictionary<string, Client>();Using this Linq query: IEnumerable<Staff> loginquery = from staff in Database.Staff where staff.Value.Passcode == txt_password.Text select staff;but i get the error Error 2 Cannot implicitly...
0
7683
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main...
0
7586
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7899
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8113
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7957
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5215
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3628
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2092
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
927
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.