Converting SQL query to linq query expression

Question

I have a SQL query that I am trying to convert into a LINQ query expression in C#, but I can not do it properly.

The query is as follows:

select 
    P.id, P.name, COUNT(S.orderqty)
from 
    product as P 
inner join 
    sales as S on P.id = S.id
group by 
    p.id, p.name

My linq code:

IQueryable<ReportType1> query = 
    from product in EngineContext.Products
    join SalesOrder inEngineContext.SalesOrderDetails  
    on product.ProductID equals SalesOrder.ProductID
    group SalesOrder.OrderQty by product into groups
    select new ReportType1() {    
        Year = null,
        ProducId = groups.Key.ProductID,
        Name = groups.Key.Name,
        Quantity = ??
    };

What to write for value of Quantity?

Backs · Accepted Answer · 2015-10-25 08:51:16Z

1

Count method of group:

select new ReportType1
{
    Year = null,
    ProducId = groups.Key.ProductID,
    Name = groups.Key.Name,
    Quantity = groups.Count()
}

answered Oct 25, 2015 at 8:51

Backs

25k5 gold badges66 silver badges92 bronze badges

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

5 Comments

Backs Over a year ago

@MohammadRezaTaghipour what do you mean? It works because it's LinqToSql and it translate methods to sql-code

MohammadReza Taghipour Over a year ago

yes,my question is not clear! i mean what does "groups.Count()" refer to? does it refer to exactly "SalesOrder.OrderQty"?

Backs Over a year ago

@MohammadRezaTaghipour it refers to your groupping by product, so it counts in every group

Ghasem Over a year ago

@MohammadRezaTaghipour Please consider checking the answer as the right answer if it was helpful to encourage more pepole to answer your questions

Backs Over a year ago

@MohammadRezaTaghipour can you accept my answer or should I improve it?

Collectives™ on Stack Overflow

Converting SQL query to linq query expression

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related