Stored procedure to LINQ

I love stored procedures and I will use them when I can. But for this particular app, I need to use LINQ. I've done LINQ before but nothing major. I have been looking grouping in LINQ and I dont quite understand it. Can someone show me how to convert this to LINQ to SQL:

select orderid, address1, count(*) as total
FROM  orderheader
where isfulfilled=0 and inprocess=0 group by orderid,address1 order by zip

I would show you what I have but im embarrassed. This is .net 4. Thanks
troycompAsked:
Who is Participating?
 
Snarf0001Commented:
Is this Linq to sql or Entity framework?

EF has a few differences, it actually translates the entire query to sql, so it might be having troubles resolving .First().
In any case, you can do just like it says, and change to:

var query = from row in ctx.GetTable<OrderHeader>()
            where row.IsFulfilled == 0 &&
                  row.InProcess == 0
            group row by new { row.OrderID, row.Address1 } into g
            orderby g.FirstOrDefault().Zip ascending
            select new
            {
                OrderID = g.Key.OrderID,
                Address1 = g.Key.Address1,
                Total = g.Count()
            };

First() will throw an exception if there's no value there, .FirstOrDefault() will return the default value of the type.

 
0
 
käµfm³d 👽Commented:
Can someone show me how to convert this to LINQ to SQL
Here's one way:

DataContext ctx = new DataContext(constr);

var query = from row in ctx.GetTable<OrderHeader>()
            where row.IsFulfilled == 0 &&
                  row.InProcess == 0
            group row by new { row.OrderID, row.Address1 } into g
            orderby g.First().Zip ascending
            select new
            {
                OrderID = g.First().OrderID,
                Address1 = g.First().Address1,
                Total = g.Count()
            };

foreach (var group in query)
{
    Console.WriteLine("Total orders: {0}\n\tOrderID: {1}\n\tAddress1: {2}", group.Total.ToString(), group.OrderID, group.Address1);
}

Open in new window



I love stored procedures and I will use them when I can. But for this particular app, I need to use LINQ.
You can still use them, for the most part. See #5 on ScottGu's blog: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx
0
 
käµfm³d 👽Commented:
Sorry, that blog isn't really that informative. This should be better (same guy):  http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
troycompAuthor Commented:
Hi kaufmed. I'll have to wait till the morning to run your code. I'm using LINQ cause the app has a ton of special queries that need to be written dynamically. I was using sprocs, but it ws becoming to cumbersome and i'm more comfortable writing c# code when the pressure is on :)
0
 
Snarf0001Commented:
One slight mod to kaufmed's query... the custom class you grouped by is available in the .Key, so you can avoid doing multiple .First() calls:

var query = from row in ctx.GetTable<OrderHeader>()
            where row.IsFulfilled == 0 &&
                  row.InProcess == 0
            group row by new { row.OrderID, row.Address1 } into g
            orderby g.First().Zip ascending
            select new
            {
                OrderID = g.Key.OrderID,
                Address1 = g.Key.Address1,
                Total = g.Count()
            };
0
 
troycompAuthor Commented:
@Snarf0001,

I saw the Key in some exaples, but I need a little explanation of it. I like to know why something works not just how. Thats how we learn
0
 
käµfm³d 👽Commented:
so you can avoid doing multiple .First() calls:
Not if you want all of the columns you can't  ; )
0
 
troycompAuthor Commented:
@kaufmed:

I do want the columns, so the key is just for aggregate functions like sum and count?
0
 
Snarf0001Commented:
Sure, basically whatever you group by gets put into the .Key field.
Once you've done a groupby into g, g is an object that holds each of the fields you grouped by as a Key property, and then still has all of the applicable rows in the enumerable value.

So this works with the implicit class { row.OrderID, row.Address1 }.  Whatever you put in the group, will be in the .Key.

0
 
troycompAuthor Commented:
@Snarf:

Can i call you that :) Was kaufmed right, you cant return columns by using key?
0
 
Snarf0001Commented:
Similar to sql, you can return all of the columns that you're groupng on.
In the case you have there where you're ordering by .zip (which isn't grouped), then you need to use .First().

But generally speaking any column you want access to is going to be part of the group anyway.

So you can select g.Key.OrderID and g.Key.Address1 and anything else you grouped on, but if you want to return isfulfilled, then you'll have to do the .First() call, which same as sql you'd have to do a subselect or a further aggregate.
0
 
Snarf0001Commented:
And yes, absolutely you can call me that ;)
0
 
käµfm³d 👽Commented:
Whatever you put in the group, will be in the .Key.
That won't give the desired functionality because it will group based on equality of the anonymous type--equality of all members listed in the anonymous type. The SQL statement is grouping based on the equality of two fields.
0
 
Snarf0001Commented:
It will function exactly the same as the sql.

In sql it will match on every unique combination of OrderID / Address1.  The linq group by will do exactly the same thing.
The actual grouping logic isn't changed between your query and mine.

The only thing I'm pointing out is that it's more efficient to return teh columns based on what's already being stored in the key, rather than hitting each enumerable to grab the first row and return from that.
0
 
Snarf0001Commented:
kaufmed, in the query you wrote, the grouping will perform as expected, just like in my modification.
But in your query:

var query = from row in ctx.GetTable<OrderHeader>()
            where row.IsFulfilled == 0 &&
                  row.InProcess == 0
            group row by new { row.OrderID, row.Address1 } into g
            orderby g.First().Zip ascending
            select new
            {
                OrderID = g.First().OrderID,
                Address1 = g.First().Address1,
                Total = g.Count()
            };

g.First().OrderID and g.First().Address1, will always return exactly the same thing as g.Key.OrderID and g.Key.Address1, it will just perform better to use the .Key, as its already been filtered and stored, requiring fewer lookups.
0
 
troycompAuthor Commented:
Wow. I'm learning so much right now. I cant rdp into my work pc, but i will run both querys and see how long it takes for each.
0
 
käµfm³d 👽Commented:
Never mind. I see what you are saying, and I agree. I think when I was writing that I was planning on returning something different. Yes, OrderID and Address1 can be pulled from the Key, but if you wanted Zip, you'd have to call First to grab it.

I think we were both talking about the same thing, just from different aspects  = )
0
 
troycompAuthor Commented:
You guys are wonderful!!
0
 
käµfm³d 👽Commented:
What I was referring to with the grouping was this:

...
group row by new { row.OrderID, row.Address1, row.Zip } into g
...

Open in new window


That would not be the same the SQL posted above.
0
 
Snarf0001Commented:
Agree 100%.  For the zip you still need to use the .First().
and TroyComp you're welcome, I'm sure we're both happy to help.

You should have a pretty good idea how to return whatever you'll need now ;)
0
 
troycompAuthor Commented:
Yes i do. I just needed one example and i have it. Knowledge is king!!
0
 
käµfm³d 👽Commented:
I'm sure we're both happy to help.
Always  = )
0
 
troycompAuthor Commented:
Good Morning Guys,

Im in dallas, so its 9:30 am here. when i run the code and try this

foreach (var group in query) //error happens here
{
    Console.WriteLine("Total orders: {0}\n\tOrderID: {1}\n\tAddress1: {2}", group.Total.ToString(), group.OrderID, group.Address1);
}

I get the following error:

The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.

It never makes it to the Console.writeLine. Please help..
0
 
troycompAuthor Commented:
My data source is EF. I'm using LINQ to SQL to talk to it (CRUD stuff). The FirstOrDefault worked fine. Thanks Snarf.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.