Initialize DbSet from query?

Question

I am trying to figure out how to make my DbSet initialization come from a custom query.

Explanations:

My DbSet will be read-only.

Lets say I have 3 tables in my Database. I wrote an SQL query that makes complex selection on the 3 tables. Now I want to use Entity Framework to query on the results of the query.

I think this is possible by creating a thing like this :

class MyCustomContext : DbContext
{
    public MyCustomContext(string connectionString) 
        : base(connectionString)
    {

    }

    public DbSet<MyQueryResultRow> ResultsRows { get; set; }
}

But I don't know how to say to the DbContext "Hey, for retrieving the rows of that DbSet, use that sql query!".

NB : I can't and I don't want to modify the database (I can't create a sql view for example).

Any idea?

Answer 1

You can just create your dbContext as normal and then use the SqlQuery for 'non entity' types.

For example:

using (var context = new myDbContext()) 
{ 
    var things = context.Database.SqlQuery<myType>( 
                       "SELECT things FROM dbo.myTables").ToList(); 
}

The key point is using context.Database then you have access to all the tables (that your SQL account has access too, of course!), and you can define your 'myType' to map the columns you're selecting.

Answer 2

Create a class which represents 1 to 1 what will be returned by the SQL.

This includes the data types.

public class CustomClass
{
    public int Id {get; set;}
    public string Name {get; set;}
    ....
}

//you  can pass in variables to this method...
public List<CustomClass> GetCustomeClass()
{
    //here you just need to ensure what you select matches the class(CustomClass).
    string query = "Select * from Table_XYS";

    List<CustomClass> res = context.Database.SqlQuery<CustomClass>(query).ToList();

    return res;

}

Assuming Table_XYS has Two Fields, Id(int) and Name(nvarchar) then this would work as is.

The main point here is that the SQL query can include joins or group bys, or what ever you want just as long as the Custom Class and the final select from the SQL have the same prop/fields.. including types. then it will bind the data for you.

Answer 3

Maybe you could try adding another layer, e.g. Repository layer:

public class MyCustomRepository
{
    private readonly MyCustomContext _context;
    public MyCustomRepository(MyCustomContext context)
    {
        _context = context;
    }

    public IEnumerable<MyQueryResultRow> GetResultRows()
    {
        return _context.ResultsRows.Where(r => r.Id > 10); // Here you can add some restrictions
    }
}

Returning IEnumerable won't execute query. Only after executing .ToList() method the query would be executed in the db, so you can easily add more clauses :

var myExtendedQueryResults = repository
    .GetResultRows()
    .Skip(5)
    .OrderBy(r => r.Name)
    .Take(10)
    .ToList();