ASP.NET CORE EF - LINQ statement

asp.net-core entity-framework-core linq

Question

I have a SQL statement to get some data from database.

select *, (SELECT count(ReagentID) FROM dbo.ReagentWarehouse WHERE dbo.Reagent.ReagentID = dbo.ReagentWarehouse.ReagentID) as InStock
from dbo.Reagent
join dbo.Supplier on dbo.Supplier.SupplierID = dbo.Reagent.SupplierID
join dbo.Unit on dbo.Unit.UnitID = dbo.Reagent.UnitID

SQL statement works just fine.

I'm trying to convert it to LINQ EF Core.

I have prepaired the following statement:

var viewModelReagents_W_Stock = from i in _context.Reagents.Include(i => i.Supplier).Include(i => i.Unit).Include(i => i.Supplier)
                              select new ReagentWStock
                              {
                                ReagentID = i.ReagentID,
                                UnitID = i.UnitID,
                                SupplierID = i.SupplierID,
                                ReagentName = i.ReagentName,
                                Supplier = i.Supplier,

                                InStock = (from p in _context.ReagentWarehouses
                                           where p.ReagentID.Equals(i.ReagentID)
                                           select p).Count()
                            };

This generates an error:

fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[0]
  An unhandled exception has occurred while executing the request
System.InvalidOperationException: No value provided for required parameter '_outer_ReagentID'.

From console LOG this LINQ was:

              SELECT [i.Supplier].[SupplierID], [i.Supplier].[Name], [i].[ReagentID], [i].[UnitID], [i].[SupplierID], [i].[ReagentShortcut], [i].[ReagentName], [i].[Number], [i.Unit].[UnitShortcut], (
                  SELECT COUNT(*)
                  FROM [ReagentWarehouse] AS [p]
                  WHERE [p].[ReagentID] = @_outer_ReagentID
              )
              FROM [Reagent] AS [i]
              INNER JOIN [Supplier] AS [i.Supplier] ON [i].[SupplierID] = [i.Supplier].[SupplierID]
              INNER JOIN [Unit] AS [i.Unit] ON [i].[UnitID] = [i.Unit].[UnitID]
              ORDER BY [i].[ReagentName]
              OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

Models:

public class Reagent
{
    public int ReagentID { get; set; }
    public int UnitID { get; set; }
    public int SupplierID { get; set; }
    public string ReagentName { get; set; }
    public Unit Unit { get; set; }
    public Supplier Supplier { get; set; }
    public ICollection<ReagentWarehouse> Warehouses { get; set; }
}

public class ReagentWarehouse
{
    public int ReagentWarehouseID { get; set; }
    public int LocationID { get; set; }
    public int ReagentID { get; set; }
    public int Number { get; set; }
    public DateTime DeliveryDate { get; set; }
    public decimal Quantity { get; set; }
    public Reagent Reagent { get; set; }
 }

Thanks

1
0
9/10/2017 11:35:26 AM

Popular Answer

First you linq query can be simplified (Include not required and InStock simpler):

var viewModelReagents_W_Stock = from i in context.Reagents
                                select new ReagentWStock
                                {
                                    ReagentID = i.ReagentID,
                                    UnitID = i.UnitID,
                                    SupplierID = i.SupplierID,
                                    ReagentShortcut = i.ReagentShortcut,
                                    ReagentName = i.ReagentName,
                                    Number = i.Number,
                                    Supplier = i.Supplier,
                                    UnitShortcut = i.Unit.UnitShortcut,
                                    InStock = i.Warehouses.Count()
                                };

Second, you probably have some more code config because your issue can not be reproduced. Where does _outer_ReagentID come from? Generated Sql I get is(and it works):

SELECT [i.Supplier].[SupplierID], [i.Supplier].[Name], [i].[ReagentID], [i].[UnitID], [i].[SupplierID] AS [SupplierID0], [i].[ReagentShortcut], [i].[ReagentName], [i].[Number], [i.Unit].[UnitShortcut], (
    SELECT COUNT(*)
    FROM [ReagentWarehouse] AS [p]
    WHERE [p].[ReagentID] = [i].[ReagentID]
) AS [InStock]
FROM [Reagent] AS [i]
INNER JOIN [Unit] AS [i.Unit] ON [i].[UnitID] = [i.Unit].[UnitID]
INNER JOIN [Supplier] AS [i.Supplier] ON [i].[SupplierID] = [i.Supplie.[SupplierID]
ORDER BY [i].[ReagentName]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
@__p_0 int, @__p_1 int, @__p_0=10,@__p_1=10

Third use newer version of NuGet libraries, at least 1.1.2, but it would be best to switch to NetCore 2.0 and EFCore 2.0

0
9/12/2017 1:23:01 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow