How to return a list of int in Entity Framework Core using FromSqlRaw?

user_4971859 0
Viewed 3520

I have this sql query which uses raw sql in Entity Framework 6 and return a list of integer.

var sqlString = "SELECT DISTINCT A.ListId FROM ABC A INNER JOIN ABCLists B ON B.Id = A.ListId"

var recordIds = context.Database.SqlQuery<int>(sqlString).ToList();

How to convert this above into Entity Framework Core 3?

I tried with EF Core, but not sure where to define it to return as list int type. Below is what I have.

var recordIds = _djContext.ABC.FromSqlRaw(sqlString).ToList();

Notes: I don't need LINQ version but only raw SQL. Thanks.

entity-framework-core
3 Answers

In fact you just have to "project" the query result with Select:

string sqlString = "SELECT A.Id FROM ABC A INNER JOIN ABCLists B ON B.Id = A.Id";
List<int> recordIds = _djContext.ABC.FromSqlRaw(sqlString).Select(abc => abc.Id).ToList();
user_34123160

In EF 3.0 you need to declare a dedicated type which is known as Keyless entity type

[Keyless]
public class SomeType
{
    public int Ids { get; set; }
}

Then you define a DbSet for it as usual

public DbSet<SomeType> SomeTypes { get; set; }

Note: [Keyless] attribute became available in EFCore 5.0. For EF 3.0 You can use fluent configuration calling .HasNoKey() method on it:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeType>().HasNoKey();
}

Then you can do something like:

var result = context.SomeTypes.FromSqlRaw(sqlString).ToList();
user_16761340
user_62795360
Related