Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Stored procedure to LINQ

Posted on 2011-10-27
24
Medium Priority
?
258 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:troycomp
  • 9
  • 8
  • 7
24 Comments
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 800 total points
ID: 37041980
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37041982
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
 

Author Comment

by:troycomp
ID: 37042229
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Snarf0001
ID: 37042267
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
 

Author Comment

by:troycomp
ID: 37042273
@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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37042282
so you can avoid doing multiple .First() calls:
Not if you want all of the columns you can't  ; )
0
 

Author Comment

by:troycomp
ID: 37042291
@kaufmed:

I do want the columns, so the key is just for aggregate functions like sum and count?
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 37042295
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
 

Author Comment

by:troycomp
ID: 37042317
@Snarf:

Can i call you that :) Was kaufmed right, you cant return columns by using key?
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 37042326
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
 
LVL 23

Expert Comment

by:Snarf0001
ID: 37042330
And yes, absolutely you can call me that ;)
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37042334
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
 
LVL 23

Expert Comment

by:Snarf0001
ID: 37042342
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
 
LVL 23

Expert Comment

by:Snarf0001
ID: 37042348
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
 

Author Comment

by:troycomp
ID: 37042355
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37042356
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
 

Author Comment

by:troycomp
ID: 37042360
You guys are wonderful!!
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37042364
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
 
LVL 23

Expert Comment

by:Snarf0001
ID: 37042379
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
 

Author Comment

by:troycomp
ID: 37042385
Yes i do. I just needed one example and i have it. Knowledge is king!!
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37042388
I'm sure we're both happy to help.
Always  = )
0
 

Author Comment

by:troycomp
ID: 37045107
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
 
LVL 23

Accepted Solution

by:
Snarf0001 earned 1200 total points
ID: 37045208
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
 

Author Comment

by:troycomp
ID: 37045306
My data source is EF. I'm using LINQ to SQL to talk to it (CRUD stuff). The FirstOrDefault worked fine. Thanks Snarf.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month11 days, 10 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question