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?

If only there were working examples somewhere on the internet. Just to point out EF6 is different to .Net 6. learn.microsoft.com/en-us/ef/ef6 vs learn.microsoft.com/en-us/ef/core/get-started/overview/… — Jodrell
– Jodrell, Commented Aug 8, 2022 at 13:57
You have referenced both EF6 and EF Core 6, you need to figure out which one you are actually using (I would guess the latter) and then you use db.Database.ExecuteSqlRaw for example, which you probably would have found if you have looked for it. — DavidG
– DavidG, Commented Aug 8, 2022 at 14:02
@DavidG I had found those, but they return the amount of rows affected and not what I need — Ja Da
– Ja Da, Commented Aug 8, 2022 at 14:31
You need to use Keyless entities, but first you need to fix your project up a bit. You have referenced both Entity Framework 6 and EFCore. Remove EntityFramework 6.4.4 from your project. learn.microsoft.com/en-us/ef/core/modeling/… — Dawood Awan
– Dawood Awan, Commented Aug 8, 2022 at 14:37

marc_s · Accepted Answer · 2022-08-08 14:59:54Z

6

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

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

public class Person
{
    public string Name { get; set; }
    public DateTime DateOfBirth { get; set; }  
}

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);
}

Then you can query the Persons like:

var paramss = new[] { new SqlParameter("@foo", 123) };

fooContext.Persons.FromSqlRaw("SELECT Name, DateOfBirth From Persons", paramss).AsEnumerable();

edited Aug 8, 2022 at 14:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 8, 2022 at 14:55

Dawood Awan

7,41710 gold badges63 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ja Da Over a year ago

This looks promising. It'll be a little while until I can see it in action, because good lord there's a lot to migrate here :v

Dawood Awan Over a year ago

@JaDa yes took me about a week to migrate our solution. But here is some docs I followed: mikee.se/posts/migrating_from_ef6_to_ef_core

Ja Da Over a year ago

well it looks like it mostly worked. I had to give some fields a [NotMapped] and now it's complaining that "Data is Null. This method or property cannot be called on Null values". Fun times, as they say!

Ja Da Over a year ago

Just call me hackerman. I added a "DTO" version of that object where I made all fields nullable. And on that a Map function that turns them into the non-DTO. Turns out that works :v

Collectives™ on Stack Overflow

How to execute SQL query and return an object?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related