CODEWITHSUNDEEP
vb.netlinqentity-frameworklinq-to-entities
user789433
user789433

Reputation: 13

Converting SQL Query to Linq Lambda statement

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

Upvotes: 1

Views: 1112

Answers (2)

ankit rajput
ankit rajput

Reputation: 182

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/

Upvotes: 0

Aducci
Aducci

Reputation: 26644

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(),
            };

Upvotes: 4

Related Questions