Ytukyg

I am trying to use .FromSql but I keep getting an exception

"Required Column Id is Missing"

Now all my Models have Id so I have no clue which one I am missing, or if I am writing my "As" statements wrong and it is not mapping properly.

            var inventoryItems = dbContext.InventoryItems.AsNoTracking().FromSql(@"SELECT Brands.Id AS BrandsId, InventoryItems.Id AS InventoryItemsId, 

              Companies.Id AS CompaniesIds, Countries.Id AS CountriesId, States.Id AS StatesId, Branches.Id AS BranchesId,

                           Branches.CountryId, Branches.StateId, States.CountryId AS StatesCountryId, InventoryItems.InventoryCategoryId, InventoryItems.BrandId, InventoryItems.BranchId, Branches.CompanyId

FROM            Branches INNER JOIN

                         Brands ON Branches.Id = Brands.Id INNER JOIN

                         Companies ON Branches.CompanyId = Companies.Id INNER JOIN

                         Countries ON Branches.CountryId = Countries.Id INNER JOIN

                         InventoryItems ON Branches.Id = InventoryItems.BranchId AND Brands.Id = InventoryItems.BrandId INNER JOIN

                         States ON Branches.StateId = States.Id AND Countries.Id = States.CountryId ).ToList();

Model example and all my models follow the same pattern

 public class InventoryItem

{

    public int Id { get; set; } //PK name





    public int InventoryCategoryId { get; set; } // FK name pattern



    public int BranchId { get; set; }

    public virtual Branch Branch { get; set; }



    public int BrandId { get; set; }

    public virtual Brand Brand { get; set; }

}

I am using FromSql because I need to do a where clause(not shown) that filters on a json column with EF core does not support.

a .FromSql query returns a single Entity Type. That query should return Id, InventoryCategoryId, BranchId, BrandId, one column for each property of Inventory Item (except for Navigation Properties).

If you want to load multiple different entity types, you can't use a single .FromSql() call. Instead use one of the methods detailed here. Especially note:

Entity Framework Core will automatically fix-up navigation properties
to any other entities that were previously loaded into the context
instance. So even if you don't explicitly include the data for a
navigation property, the property may still be populated if some or
all of the related entities were previously loaded.

So you can fetch the Entities you need across multiple seperate queries if you want. But this "fix-up" doesn't happen if you suppress change tracking on your Entities, as it's the change tracker that does this.