Linq to SQL use view instead of query

81 views Asked by At

I have an entity in linq to SQL that i query. The query result, of the kind IQueryable is passed through repository and is manipulated in various way.

I noticed that the first query i do, to fetch the first IQueryable is not efficient due to poor ORM translation (and there is nothing more i can do from ORM side), and i would like to use a view instead. But i don't know how to plug in a view so that it will produce a IQueryable<MyTable> instead of IQueryable<MyView>.

Here an example :

public class MyRepository
{
     MyDataContext _dataContext = new MyDataContext();

     public IQueryable<MyTable> ComplexRead (..parameters..)
     {
          return _dataContext.MyTables.Where (..something too complex..); //i would change this query with a view
     }

     public IQueryable<MyTable> Paging (IQueryable<MyTable> source, int page, int pageSize )
     {
          return source.Skip(page*pageSize).Take(pageSize);
     }
}


public class Program 
{
    static void Main(string[] args)
    {
        var repository = new MyRepository();
        var query = repository.ComplexRead (..params..);
        var result = repository.Paging (query ,0,10);
    }
}

As you can see i need to pass IQueryable arround (in a complex project so not easily replaceable), then consider that the Linq table of MyTable will load data with some prefetch (so all the dependant entity are fetched at once). Because ComplexRead is not optimizable enought with linq (take this fact as granted) i need to replace it with a view. The veiw return the same structure that the un-optimized ORM generated query will (tested with SQL Profiler) so in theory can be replace the table (with all prefetch); but i don't know how to tell to ORM to use that view to fetch MyTable data.

0

There are 0 answers