How can I translate the following SQL query to Linq-CodePudding

I have this query running ok in a stored procedure but now I want to do what sp does from c# with EF and linq, any idea?

I'm using .Net 6 in MVC EF project. I have my db context working and entities for Productos and AlmacenesStock created

The query:

SELECT s.ProductoId, p.Descripcion, SUM(s.Cantidad) AS Cantidad
FROM Productos p INNER JOIN AlmacenesStock s
ON p.Id = s.ProductoId
GROUP BY s.ProductoId, p.Descripcion

Thanks!

CodePudding user response：

Assume that in your DbContext you have these DbSet:

public DbSet<Producto> Productos { get; set; }
public DbSet<AlmacenesStock> ProductoAlmacenesStocks { get; set; }

With LINQ query syntax/expression which has some similarities with SQL query.

var result = (from a in _context.Productos
    join b in _context.AlmacenesStocks on a.Id equals b.ProductoId
    group new { a, b } by new { b.ProductoId, a.Descripcion } into g
    select new
    {
        ProductoId = g.Keys.ProductoId,
        Descripcion = g.Keys.Descripcion,
        Cantidad = g.Sum(x => x.b.Cantidad)
    }
)
.ToList();

The above result will return the value with the List of anonymous type. If you have your concrete class to store the value, modify the select part as:

select new YourEntity
{
    ProductoId = g.Keys.ProductoId,
    Descripcion = g.Keys.Descripcion,
    Cantidad = g.Sum(x => x.b.Cantidad)
}

Recommended reading: Query expression basics

CodePudding user response：

Make sure you set up the relationship of Products and AlmacenesStock

var products = _context.Products.Include(product => product.AlmacenesStock)
                                .GroupBy(product => new { product.ProductId, product.Description }
                                .Select(product => new { product.Key.ProductId, product.Key.Description, x.Sum(almnacenesStock => almnacenesStock.Cantidad) });