I want to Convert my LINQ query to PostgreSQL query

Question

var barcodePost = await _barcodePostRepository.FindAsync(barcodeSpec);
var barcodeQuantity = barcodePost?.GroupBy(b => b.BarcodeNumber)
                                   .Select(g => new { BarcodeNumber = g.Key, Quantity = g.Sum(b => b.Quantity) })
                                   .ToDictionary(g => g.BarcodeNumber, g => g.Quantity) ?? new Dictionary<long, decimal>();
               

var result = (from data in barcodeData
              join material in materialDetails on data.ProductId equals material.ProductId
              join barcode in barcodeQuantity on data.Barcode equals barcode.Key into barcodeGroup
              from barcode in barcodeGroup.DefaultIfEmpty()
              where material.Quantity - barcode.Value > 0
              select new VendorWiseStockDto()
              {
                  VendorName = vendor.Name,
                  CurrentStock = material.Quantity - barcode.Value,
              }).ToList();

This is the LINQ Query I have written I want to convert it into PostgreSQL raw query how can I do it or write the raw query.

public async Task<IList<VendorWiseStock>> GetVendorWiseStocksAsync(Guid vendorId)
           => await( from data in _dbContext.MaterialInwardBarcodes
                      join material in _dbContext.MaterialInwardDetails on data.ProductId equals material.ProductId
                      join barcode in _dbContext.BarcodePost on data.Barcode equals barcode.BarcodeNumber into barcodeGroup
                      join vendor in _dbContext.Vendors on data.VendorId equals vendor.Id
   );
}

This is what I have tried and I am having no Idea how I can write that query into raw PostgreSQL

Can you show your attempt for raw query? This should be not much complex with set of JOINs. — Yong Shun, Aug 16 '23 at 06:19
I'm not sure exactly what you're asking. That second code snippet looks like you're using Entity Framework. That's hardly a "PostgreSQL raw query". Please be clear about what you're actually trying to achieve, including your tags. Are you trying to write a SQL query or a LINQ to Entities query for EF? — jmcilhinney, Aug 16 '23 at 06:19
Possible duplicate: [Get SQL code from an Entity Framework Core IQueryable](https://stackoverflow.com/questions/37527783/get-sql-code-from-an-entity-framework-core-iqueryablet) — Yong Shun, Aug 16 '23 at 06:31

score 0 · Answer 1 · edited Aug 16 '23 at 06:28

var barcodePost = await _barcodePostRepository.FindAsync(barcodeSpec);
var barcodeQuantity = barcodePost?.GroupBy(b => b.BarcodeNumber)
                   .Select(g => new { BarcodeNumber = g.Key, Quantity = g.Sum(b => b.Quantity) })
                   .ToDictionary(g => g.BarcodeNumber, g => g.Quantity) ?? new Dictionary<long, decimal>();


var result = (from data in barcodeData
              join material in materialDetails on data.ProductId equals material.ProductId
              join barcode in barcodeQuantity on data.Barcode equals barcode.Key into barcodeGroup
              from barcode in barcodeGroup.DefaultIfEmpty()
              where material.Quantity - barcode.Value > 0
              select new VendorWiseStockDto()
              {
                  VendorName = vendor.Name,
                  CurrentStock = material.Quantity - barcode.Value,
              });

In your above query I removed ToList(). so now it is IQueriable

Now Run your query and check barcodePost in debug mode it will give raw query

I want to Convert my LINQ query to PostgreSQL query

1 Answers1