How to return a subquery in LINQ/EF

Question

I have the following method who return a SQL subquery. With the method's return I elaborate the main query.

But now I need to do this using a LINQ query.

How can I do it?

Public void AvailableStock()
{
        string query = "Select prod.ID, prod.Name, ";
        query += AvailableStockQuery("prod.ID") + " as AvailableStock ";
        query += " From TAB_Products prod ";
}
Public string AvailableStockQuery(string ProductAlias = "prod.ID")
{
        string query = "((Select Sum(est.Quantity) " +
                       "  From ProductStock est " +
                       "  Where est.ProductID = " + ProductAlias +
                       " ) " +
                       " - (Select Sum(it.Quantity) " +
                          " From OrderItens it " +
                          " Where it.ProductID = " + ProductAlias + 
                          ")" +
                       ") ";

        return query;
}

You need to work with the `IQueryable` interface. In this case it will be harder because you are changing the result object, not the filtering portion. https://stackoverflow.com/questions/5541234/creating-dynamic-queries-with-entity-framework — EzLo, Feb 13 '19 at 16:05
Could you edit your question and provide us your entity classes and the relations between them. It seems you have TAB_Products, ProductStock, OrderItems, but how are they related? Can you describe the requirement of your query? "Give a string ProductAlias, give me all ... from ... that ... — Harald Coppoolse, Feb 14 '19 at 08:40

score 0 · Answer 1 · answered Feb 14 '19 at 14:09

But, you don't even need a sub query here. You could just join ProductStock and OrderItens tables to the TAB_Products table, and group by prod.ID and then the sub queries are not needed at all. Probably the performance is also better. And it is easier to translate this to EF because there are no sub queries.

Something like this:

SELECT prod.ID, FIRST(prod.Name), (SUM(est.Quantity) - Sum(it.Quantity)) AS AvailableStock 
From TAB_Products prod 
LEFT JOIN ProductStock est ON est.ProductID = prod.ID
LEFT JOIN OrderItens it ON it.ProductID = prod.ID
GROUP BY prod.ID

But, if you want you can make subqueries also, here is an example: https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-a-subquery-on-a-grouping-operation

How to return a subquery in LINQ/EF

1 Answers1