Converting SQL Query to Linq Lambda statement

Question

I am trying to convert the the following SQL statement into a linq query but for some reason I cannot get it to work!!

SELECT     o.ITEMID, COUNT(o.ITEMID) AS COUNT, MAX(i.QUANTITY) AS Quantity
FROM       ORDERS AS o LEFT OUTER JOIN
           INVENTORY AS i ON o.ITEMID = i.ITEMID
           GROUP BY o.ITEMID

I found this link, somebody having a similar problem but I cant seem to apply this to what i need.

thanks for all your help.

This is the code i have so far

Dim castleavailability = _
            From o In orders _
            From i In inventorys _
            Where (x >= o.ITEMID = i.ITEMID)
            Group New With {o, i} By o.ITEMID Into oi()
            Select New With {.ItemId = oi.Key, .Count = oi.Select(y >= y.o.ItemId).Count(), .Quantity = oi.Select(y >= y.i.Quantity).Max()}

the error I am getting now is "Definition of method 'oi' is not accessible in this context." referring to the "group new with" line. Any ideas on how to resolve this

Many Thanks

What have you tried so far? And "it doesn't work" isn't an error description! Doesn't it compile? Does it throw a runtime exception? Does it run but return wrong results? — Daniel Hilgarth
– Daniel Hilgarth, Commented Mar 7, 2012 at 14:58
Is it Linq-To-Sql or Linq-To-Entities? They are not the same thing. — cadrell0
– cadrell0, Commented Mar 7, 2012 at 15:02
Hi sorry it is linq to Entities. I didnt mean to click Linq to SQL — user789433
– user789433, Commented Mar 7, 2012 at 15:23

Aducci · Accepted Answer · 2012-03-07 14:58:30Z

4

This should work for you

var query = from o in context.Orders
            from i in context.Inventory
                             .Where(x = > o.ItemId = x.ItemId)
                             .DefaultIfEmpty()
            group new { o, i } by o.ItemId into oi
            select new
            {
              ItemId = oi.Key,
              Count = oi.Select(y => y.o.ItemId).Count(),
              Quantity = oi.Select(y => y.i.Quantity).Max(),
            };

answered Mar 7, 2012 at 14:58

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user789433 Over a year ago

Wow that was super fast thanks very much i will test this now.

user789433 Over a year ago

Hi I am coding in vb so have changed this slightly to get rid of the errors showing in VS i have Dim castleavailability = _ From o In orders _ From i In inventorys _ Where (x >= o.ITEMID = x.ItemId) group new { o, i } by o.ItemId into oi select new {ItemId = oi.Key, Count = oi.Select(y => y.o.ItemId).Count(), Quantity = oi.Select(y => y.i.Quantity).Max()} I am getting the error Type or 'with' expected at 'group new { ' Thanks

Daniel Hilgarth Over a year ago

@user789433: Change every new { to New With { and add a dot in front of the left hand side of the inner expressions, e.g. group New With { o, i } and select New With {.ItemId = oi.Key, .Count = .... See here for more info.

ankit rajput · Accepted Answer · 2012-03-15 12:11:13Z

0

You can also use Linqer software to convert sql query to Linq Lambda query.

You can get this software from following link:

http://www.sqltolinq.com/

answered Mar 15, 2012 at 12:11

ankit rajput

1822 silver badges5 bronze badges

1 Comment

AlexGH Over a year ago

I'm using Linqer but can't find any choice to translate SQL to Linq using Lambda expression, just classic Linq

Collectives™ on Stack Overflow

Converting SQL Query to Linq Lambda statement

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related