How can I transalte SQL Query to LINQ

Question

I trying to translate SQL Query to Linq statement:

 SELECT f.BarcodeNumber,m.Name, f.Model, SUM(f.Quantity) as FoundedAssetsQty, ISNULL(a.Quantity,0) as AssetQty
  FROM [InventoryDB].[dbo].[FoundedAssets] f
  join [InventoryDB].[dbo].[PhisicalStockCheckSheets] p on p.ID = f.PhisicalStockCheckSheetId
  join [InventoryDB].[dbo].[Inventories] i on i.ID = p.InventoryId
  left join [InventoryDB].[dbo].[Assets] a on a.BarcodeNumber = f.BarcodeNumber
  join [InventoryDB].[dbo].[Manufacturers] m on m.ID = f.ManufacturerId
  where p.InventoryId = 10
  group by f.BarcodeNumber, a.Quantity, f.Model, m.Name

I have no idea how to do it. I tried many ways but I fail. Could anyone help me?

Check out [these tools](https://stackoverflow.com/questions/296972/sql-to-linq-tool) for converting SQL into Linq. Hope it helps. — derloopkat, Jul 06 '20 at 18:20

Tchaikovsky · Answer 1 · 2020-07-07T08:08:16.947

I tried to use Linqer, but when I configure the connection it fails, so I write the linq instruction myself. Finally I found the answer. I have not mentioned the relations between entities but it is not important here.

var summary = _context.FoundedAssets.Include(f => f.Manufacturer).
                Include(f => f.Asset).
                Include(f => f.PhisicalStockCheckSheet).ThenInclude(f => f.Inventory).
                Where(f => f.PhisicalStockCheckSheet.Inventory.ID == id).
                Select(x => new InventorySummaryModel()
                {
                    BarcodeNumber = x.BarcodeNumber.Value,
                    ManufacturerName = x.Manufacturer.Name,
                    Model = x.Model,
                    AssetsQuantity = x.Asset.Quantity,
                    FoundedAssetQuantity = x.Quantity
                }).ToList();

            var groupedSummary = summary.GroupBy(x => x.BarcodeNumber).Select(x => new InventorySummaryModel()
            {
                BarcodeNumber = x.First().BarcodeNumber,
                ManufacturerName = x.First().ManufacturerName,
                Model = x.First().Model,
                FoundedAssetQuantity = x.Sum(a => a.FoundedAssetQuantity),
                AssetsQuantity = x.First().AssetsQuantity
            }).ToList();

Maybe exists any easier approach but this one works properly.

How can I transalte SQL Query to LINQ

1 Answers1