I'm using entity framework code first to query database, the problem is I'm not allowed to create views in the database, so I should use query scripts in my codes. I want this query script to be considered same as DBSet or IQueryable by entity framework so that I can apply linq queries on it. I don't want it to be IEnumerable so 'Database.SqlQuery' doesn't work for me.
example: there is Product table in db, which contains {Id,Name,CategoryId} and there is Category table in db, which contains {Id,Name}
my query script is like this:
select Id,Name,CategoryId,CategoryName
from Product p
join Category c on p.CategoryId=c.Id
I want to use this query in my code, as if it was a view.
Any idea?
ps: The query script is so complicated that I cannot build it with Linq.
I solved this problem using Interceptor, Here is the solution:
Create a Dbset in your context like this:
public DbSet<Model.MyView> MyViews { get; set; }Create DbCommandInterceptor, and apply it to your entity framework configuration
In the Interceptor implement this method:
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext), and modify the command text like this:command.CommandText = command.CommandText.Replace("[dbo].[MyViews]", "(" + MyViewScript + ")");
