Querying Entity Framework Core Without DbSet

Question

I'm working on a data-driven solution using Entity Framework Core. I dynamically create SQL Server tables using ExecuteRawSQL() and build the query string based on the contents of a file I am reading in. I would like to query those tables but I do not have DbSets for them because they were created dynamically. I tried using: ExecuteRawSQL("SELECT * FROM ...") but this only returns the number of rows affected.

Ideally, I would like to get each one of the rows back from this query in the form of Dictionaries (key = column name, value = column value). Any ideas? Thanks!

@ErikEJ I went down that path and it worked like a charm! Thank you! — Benjamin Gruher
– Benjamin Gruher, Commented Jun 6, 2021 at 7:05

Gustavo de Lima · Accepted Answer · 2021-06-08 04:00:15Z

In EF Core you can use the DbContext Set<T>() method and then FromRawSql() to retrieve data and translate the results into a domain class that is not registered as a DbSet<T>.

In this example, I created a Cat object to represent a row in the Cats table:

public class Cat
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Breed { get; set; }
}

Now EF Core knows how to project the results:

var context = new SampleDbContext();

var cats = context
    .Set<Cat>()
    .FromSqlRaw("SELECT * FROM Cats")
    .ToList();

However, you'll have to tell EF Core to map that object to the database table on your DbContext class:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Cat>().ToTable("Cats");
}

It's also important that the object you're projecting the results to has the exact same properties as the table you're pulling the data from, otherwise you'll get an exception.

I'm not sure how you would format the results as a dictionary, but it's possible to use Select() after FromRawSql().

var links = context.Set<Cat>()
    .FromSqlRaw("SELECT * FROM Cats")
    .Select(c =>
        new
        {
            Key = nameof(c.Id),
            Value = c.Id
        });

Collectives™ on Stack Overflow

Querying Entity Framework Core Without DbSet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related