CODEWITHSUNDEEP
c#linq
sony
sony

Reputation: 1503

Convert SQL statement to Linq?

I have a table named Outweigh which has around 50 fields. I need to create a Linq query which is like this SQL query.

SELECT DISTINCT Product, 
   Medication, 
   SUM(NettWeight) as NettWt, 
   COUNT(DISTINCT CustCode) as Customer, 
   COUNT(DISTINCT DktNo)as DktNo
FROM outweigh
GROUP BY Product, Medication
ORDER BY Product, Medication

The result is below:

enter image description here

I have written some code as below which is not very efficient. 

lstOutWeigh = dbContext.Outweighs
                       .Where(o => o.DateOut >= StartDate && o.DateOut <= EndDate)
                       .ToList();

  var k = from t in lstOutWeigh select new {t.Product,t.Medication,t.NettWeight};
  var l  = from t in k
           group t by new { t.Product, t.Medication } into g
           select new someclass
           {
               prod =g.Key.Product,
               med = g.Key.Medication,
               tonnes = (double) g.Sum(x => x.NettWeight),
               count = g.Count()
           };

Any suggestions?

Upvotes: 0

Views: 1051

Answers (3)

Jodrell
Jodrell

Reputation: 35716

Something like,

db.Outweighs.GroupBy(ow => new { ow.Product, ow.Medication })
    .OrderBy(g => g.Key)
    .Select(g => new
        {
            g.Key.Product,
            g.Key.Medication,
            NettWt = g.Sum(ow => ow.NettWeight),
            Customer = g.Select(ow => ow.CustCode).Distinct().Count(),
            DktNo = g.Select(ow => ow.DktNo).Distinct().Count()
        })

is equivalent to the SQL you present in the question. However, the Linq-To-Sql you present doesen't match up.

Upvotes: 1

Mostafa Soghandi
Mostafa Soghandi

Reputation: 1594

     var t = (from  p in Context.outweigh
              group p by new {p.Product,p.Medication}  into g
              select new {Product= t.Product,Medication=t.Medication, NettWt = g.Sum(x => x.NettWeight),Customer=g.Count())
             .Distinct().orderby(new {p.Product, p.Medication});

Upvotes: 0

Ricky Mutschlechner
Ricky Mutschlechner

Reputation: 4409

As someone mentioned, http://sqltolinq.com/ works well.

http://www.linqpad.net/ is also another great tool (that I/we personally use where I work) that can help you convert between those types of statements - even using Lambda expressions if you'd like - which in my opinion is one of the easiest ways to query using LINQ.

Upvotes: 1

Related Questions