Load a not mapped attribute with EF Core

user_5607537 0
Viewed 11618

I try to load an entity with EF Core by calling a stored procedure. The entity is mapped to a table by fluent mapping which does not contain all columns which are selected by the stored procedure.

The selected fields which do not exist as table columns are ignored in the entity configuration like this:

        modelBuilder.Entity<CustomerLocationEntity>().Ignore(c => c.Latitude);
        modelBuilder.Entity<CustomerLocationEntity>().Ignore(c => c.Longitude);
        modelBuilder.Entity<CustomerLocationEntity>().Ignore(c => c.Radius);
        modelBuilder.Entity<CustomerLocationEntity>().Ignore(c => c.Distance);

The stored procedure is called like this:

await SalesContext.CustomerLocation
            .FromSql("GetCustomersByLocation @latitude={0}, @longitude={1}, @radius={2}", lat,
                lon, radius)
            .ToListAsync();

When the query is executed the ignored columns are not mapped to the entity. Is there any possibility to map the ignored fields to the entity when calling a stored procedure or do I have to create another entity for the stored procedure or something like this?

entity-framework-core entity-framework c#
1 Answers

When you use the ignore method of fluent api on column, it will not create that column in sql server (Ignores it).
Your stored procedure result will give you come some columns based on your created query and these column names must match the property names on your entity.
For example your procedure gives you a table with these columns and also matched data types in sql server and your class:

  1. Latitude
  2. Longitude
  3. Radius
  4. Distance

Then you should create a class for your procedure:

public class LocationProcedure {
   public string Latitude {get;set;}
   public string Longitude {get;set;}
   public string Radius {get;set;}
   public string Distance {get;set;}
}

and also add a dbset of this type to your dbContext with [NotMapped] Attribute:

[NotMapped]
public DbSet<LocationProcedure> CustomerLocation {get; set:}

The attribute tells that this should not be a table in database.

Finally you can use your procedure with the new dbset CustomerLocation and then it will map the results to LocationProcedure class.

await SalesContext.CustomerLocation
            .FromSql("GetCustomersByLocation @latitude={0}, @longitude={1}, @radius={2}", lat,
                lon, radius)
            .ToListAsync();
user_102116200
Related