How to execute SQL query and return an object?

Question

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 docs say. I know I can do db.[TableName].FromSqlRaw, but I can't use that because the object I need isn't in the database.

I've got the following packages but I may be missing something?

Answer 1

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://learn.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();