Is it possible to create a dynamic query with Entity Framework. I Have 18 tables, Each of them has the same structures. How can I create a dynamic query to reuse the same query for each tables. I would like to have a generic query for Create Read Update Delete. The read contains the same "Where" Clause. Thanks for your help.
Add a comment
|
1 Answer
Here you have simple example for pure CRUD scenario. Create interface which will contain shared properties for your queries. Implement this interface in all your entity classes. Than create repository. Repository is usually defined as generic but in your case I defined each method as generic so that you can use same repository instence for all entities.
public interface IWellKnownEntity
{
int Type { get; set; }
}
public class Repository
{
public T GetEntityByWellKnownQuery<T>() where T : IWellKnownEntity
{
using (var context = new MyContext())
{
return context.CreateObjectSet<T>().FirstOrDefault(e => e.Type == 1);
}
}
public IEnumerable<T> GetEntitiesByCustomQuery<T>(Expression<Func<T, bool>> where)
{
using (var context = new MyContext())
{
return context.CreateObjectSet<T>().Where(where).ToList();
}
}
public void Create<T>(T entity) where T : IWellKnownEntity
{
using (var context = new MyContext())
{
context.AddObject(entity);
context.SaveChanges();
}
}
public void Update<T>(T entity) where T : IWellKnownEntity
{
using (var context = new MyContext())
{
context.Attach(entity);
context.ObjectStateManager.ChageObjecState(entity, EntityState.Modified);
context.SaveChanges();
}
}
public void Delete<T>(T entity) where T : IWellKnownEntity
{
using (var context = new MyContext())
{
context.Attach(entity);
context.DeleteObject(entity);
context.SaveChanges();
}
}
}
Than you suppose that you have entity product and catebory which impelment well known interface. You can simply call:
var repository = new Repository();
var product = repository.GetEntityByWellKnownQuery<Product>();
product.Name = "Updated";
repository.Update<Product>(product);
var category = repository.GetEntitiesByCustomQuery<Category>(c => c.Id == 1).First();
repository.Delete<Category>(category);
You can futher improve the sample code. This code doesn't use shared context so it is more usable for disconnected scenario (web application). If you use connected scenario like WinForms application or batch application you can implement IDisposable on repository and share context among all methods. Dispose method on repository will hanlde disposing of context. Code for Update and Delete methods will be different because there is no need to attach entity back to context or set entity state.
4 Comments
mrtaikandi
I've done what you said but I get this exception: Unable to cast the type 'MyNamespace.MyEntity' to type 'MyNamespace.IWellKnownEntity'. LINQ to Entities only supports casting Entity Data Model primitive types.
Ladislav Mrnka
Can you post some code? You should not work with IWellKnownEntity but with MyEntity directly without any casts.
mrtaikandi
I've checked my code again, the only difference is that when querying you used, CreateObjectSet<T> but I used CreateQuery<T>. I haven't checked if its the problem or not. But I have a question though, when EF generates ObjectContext for my model it creates object sets. When you use CreateObjectSet<T> again on the GetEntityByWellKnownQuery method, wouldn't it creates another instance of the T?
mrtaikandi
Another thing is that the defination of the CreateObjectSet is "public ObjectSet<TEntity> CreateObjectSet<TEntity>() where TEntity : class;" which accepts only reference types but the type of T in your query is not.