FromSqlRaw() doesn't work with TPT

[dantey89]

It is impossible to use FromSqlRaw() when using TPT approach.

public class BaseUser 
{
       public Guid Id { get; private set; } = Guid.NewGuid();

       public string IdentityUserId { get; set; }
}

public class CustomerUser: BaseUser
{
        public string Email { get; set; }
}

public class User : BaseUser
{
        public string Name { get; set; }

        public string JsonPermissions { get; set;}
}

public class Technician : User
{
                   public bool PriorityFlag { get; set; }
}

And EF configuration is next:

 protected override void OnModelCreating(ModelBuilder builder)
{
             builder.Entity<User>()
                .ToTable("Users");

          builder.Entity<CustomerUser>()
                .ToTable("CustomerUsers");

           builder.Entity<Technician >()
                .ToTable("Technicians");
} 

Now when I try to execute my code

string customSQL = @"
    SELECT u.*, 
    NULL as Discriminator 
    from 
   [Users] as u";

Context.Set<User>()
    FromSqlRaw(query.ToString());

I'm getting an error: "Using 'FromSqlRaw' on DbSet of 'User' is not supported since 'User' is part of hierarchy and does not contain a discriminator property."

After some researches, I found that plain Linq and FromSqlRaw() method handle discriminator identifying in different ways. For regular Linq exists 'else' statement which joins tables to get discriminator (Image) value, while FromSqlRaw() only throws an exception when no discriminator us declared (Image)

[ajcvickers]

This is tracked by #21627; please vote for that issue.

[gojanpaolo]

I think creating a separate C# class. is a possible workaround for some use cases.

public class UserFromSql
{
    public Guid Id { get; set; }
    public string IdentityUserId { get; set; }
    public string Name { get; set; }
    public string JsonPermissions { get; set; }
}

Context.Set<UserFromSql>().FromSqlRaw("select * from users");

---

If doing code-first then you might need to tell EF that the entity (UserFromSql) doesn't map to a db table either by setting ToView(null) or ToTable(null).

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<UserFromSql>().ToView(null);
}

[gojanpaolo]

@ajcvickers Sorry for asking here but in our application, it looks like we ultimately just need an IQueryable<string>, is it possible to run a raw sql query from EF Core without an entity?

Something like this but this doesn't work

ctx.Set<string>().FromSqlRaw("select id from ...");

Thanks in advance!

[roji]

@gojanpaolo that would be #11624, which isn't supported yet by EF Core. You can easily drop down to ADO.NET or use Dapper to execute that query.

[gojanpaolo]

@roji thanks for the response and linking the open issue. I think I'll keep the class with a string property instead of adding a new library to the project. Also, I need to chain the IQueryable<string> with more ef queries.

[jpodpro]

I'm trying to execute an SQL query using FromSql with a custom "Where" condition and I'm getting the same error. Even if I'm not attempting any kind of hierarchical join I get the same error simply because the single table is part of a hierarchy. This seems counter-intuitive and the result is that I'm forced to execute 2 queries using EF Core where I can easily write the SQL as 1.

var tracks = await _context.Tracks.FromSql($"""
   SELECT * 
   FROM Tracks t
   WHERE t.Location.STDistance(geography::Point({latitude}, {longitude}, 4326)) < 2500
""")
.ToListAsync();