-1

enter image description here

I'm trying to get the result of the query into a custom model that I created but I get an error:

Cannot create a DbSet for 'POEntry' because this type is not included in the model for the context.

That's because POEntry is a custom model to get the data from the query.

I also tried to use FromSqlInterpolated and I get the same error

 public async Task<List<POEntry>> GetClosedTransferInsEntries()
        {
            
           var TRInsClosed = @" SELECT e.ID as ID,e.PurchaseOrderID as OID,e.ItemID as ItemID,e.ItemDescription as ItemDescription,i.Quantity as QuantityOnHand, " +
           " (case when po.Remarks like 'COR%' then 'COR' else '' end) as COR," +
           " e.QuantityOrdered,sl.MasterPackQuantity as MasterPackQuantity," +
           " i.ItemLookupCode as ItemLookupCode,e.QuantityReceivedToDate as QuantityReceived," +
           " 1 as ReasonID," +
           " (case when po.ConfirmingTo = 'Done' then CAST(1 AS BIT) when po.ConfirmingTo = '' then CAST(0 AS BIT)  end) as IsSyncHQ," +
           " (case when i.ItemType = 6 then CAST(1 AS BIT) when ((i.ItemType = 0 or i.ItemType = 3) and i.ItemLookupCode like '20%') then CAST(1 AS BIT) else CAST(0 AS BIT) end ) as Weight," +
             " (case when (i.DepartmentID = 289 or i.DepartmentID = 258) then CAST(0 AS BIT) else CAST(1 AS BIT) end ) as HasDate, " +
           " i.SupplierID as SupplierID, (case when  (select Sum(Quantity) from HH_Production  where ItemID = e.ItemID group by ItemID) is null then 0 else (select Sum(Quantity) from HH_Production  where ItemID = e.ItemID group by ItemID)  end) as TotalQuantity ,(case when aa.Alias is null then ''  else aa.Alias end )as Alias, (case when IsNull(CONVERT(datetime,  hp.ExpireDate),'')='' then ' ' else Convert(varchar(50), hp.ExpireDate,101) end) as ExpireDate ," +
           " (case when IsNull(CONVERT(datetime,  hp.ProductionDate),'')='' then ' ' else Convert(varchar(50), hp.ProductionDate,101) end) as ProductionDate " +
           " FROM PurchaseOrderEntry AS e Inner JOIN PurchaseOrder AS po ON e.PurchaseOrderID = po.ID Inner JOIN Item AS i ON e.ItemID = i.ID" +
           " Left JOIN Alias as aa  ON e.ItemID = aa.ItemID  Left JOIN HH_Production as hp  ON po.ID = hp.PurchaseOrderID  and hp.ItemID = e.ItemID Inner JOIN SupplierList as sl  ON e.ItemID=sl.ItemID " +
           " WHERE(po.POType = 2 or po.POType = 4) AND(po.Status = 2) and po.SupplierID = 0 And (po.DateCreated > DATEADD(DAY, -10, GETDATE())) order by po.DateCreated ";

var mm = db.Set<POEntry>().FromSqlRaw(TRInsClosed).ToList();
return mm;
}

i have the sql query in a string and i want to execute it and get the result in a model that i created to receive the data selected from the query

Mina Latif
  • 1
  • 1
  • Please post your code and not an image of your code. – Mr.Jones Aug 01 '23 at 08:59
  • 1
    Please refer to [Keyless Entity Types](https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=data-annotations). – Ivan Stoev Aug 01 '23 at 09:27
  • 1
    Please provide enough code so others can better understand or reproduce the problem. – Community Aug 01 '23 at 09:34
  • i added the code of the action – Mina Latif Aug 01 '23 at 13:07
  • i do curious which part of the code snippet above that uses [tag:linq]? it does not involve translating `linq` into sql query.. also, we are not quite sure how `POEntry` looked like or how the query result looks like. – Bagus Tesa Aug 01 '23 at 13:10
  • Does this answer your question? [Raw SQL Query without DbSet - Entity Framework Core](https://stackoverflow.com/questions/35631903/raw-sql-query-without-dbset-entity-framework-core) – Bagus Tesa Aug 01 '23 at 13:24
  • Your SQL string should just be a single, multi-line string with an @ in front of it. Concatenating strings this way is too error-prone. – Robert Harvey Aug 01 '23 at 22:06
  • These methods need to be used directly on a DbSet, Your `POEntry` model is not an original entity in dbcontext. – Xinran Shen Aug 02 '23 at 02:03
  • @BagusTesa yes that answer worked for me. Thank you. – Mina Latif Aug 02 '23 at 07:23

1 Answers1

1

Consider creating a view for the base query to return this POEntry expected data type, then set up the DbContext to resolve a POEntry from the view. For instance I would consider creating the view with the provided SQL minus the PO.Status check, then add a DbSet to the DbContext called POEntries configured to go to the new view. (I.e. vwPOEntry) Then when I wanted to retrieve only Closed POEntries:

var entries = _context.POEntries
    .Where(x => x.Status == 2) // Ideally with a constant or Enum for the status.
    .ToList();

In this way the view can be used to retrieve closed, open, etc. entries.

However if you want to filter values like department and other details then be sure to add those columns to the view and apply the filters to your Linq expression. I do not recommend using FromRawSql and the like, they are provided for truly exceptional cases, and you greatly risk introducing issues like SQL injection attacks if you find you are relying on them. EF is a relational DB mapper and should be leveraged using navigation properties and the like rather than just used as a substitute for ADO.

Steve Py
  • 26,149
  • 3
  • 25
  • 43