Home > other >  Entity Framework Core 6 - How on earth to you execute an SQL query and return an object?
Entity Framework Core 6 - How on earth to you execute an SQL query and return an object?

Time:08-11

So I'm migrating from .NET Framework to .NET 6. I'm in a function where the guy I took over from spends line 693 - 760 generating a query in SQL and getting the result with this:

var result = db.Database.SqlQuery<CustomObject>(sqlQuery.ToString(), parameters.ToArray());

But I am very kindly being informed that:

'DatabaseFacade' does not contain a definition for 'SqlQuery' and no accessible extension method 'SqlQuery' accepting a first argument of type 'DatabaseFacade' could be found (are you missing a using directive or an assembly reference?)

I am using System.Data.Entity as the ducks EntityFramework (6.4.4) Microsoft.EntityFrameworkCore (6.0.7) Microsoft.EntityFrameworkCore.Design (6.0.7) Microsoft.EntityFrameworkCore.Relational (6.0.7) Microsoft.EntityFrameworkCore.SqlServer (6.0.7)

CodePudding user response:

So I recently went through the same process migrating from EF to EF Core.

If your Entity is NOT in the database, and is just based on an SQL query, you can use Keyless entities and map to a NULL view. Docs: https://docs.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=data-annotations

  1. Add your entity class (which should match the result from query)

    public class Person
    {
        public string Name { get; set; }
        public DateTime DateOfBirth { get; set; }  
    }
    
  2. Add the collection to your DbContext as a DbSet:

    public class FooContext : DbContext
    {
        ....
        public virtual DbSet<Person> Persons { get; set; }
    }
    

    To make the entity keyless, you can either use the [Keyless] attribute, or in Fluent syntax in DbContext:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
                 .Entity<Person>()
                 .HasNoKey()
                 // ToView should be NULL, or you can Map to an SQL View here
                 .ToView(null);
    }
    
  3. Then you can query the Persons like:

    var paramss = new[] { new SqlParameter("@foo", 123) };
    
    fooContext.Persons.FromSqlRaw("SELECT Name, DateOfBirth From Persons", paramss).AsEnumerable();
    
  • Related