Generic query in EntityFramework with 'where' statement

Question

I need to create a generic method to fetch some data related to another entity.

This is how my code looks like at the moment:

public static void InvalidateData<TEntity>(string foreignKey, int valueFK)
{
    try
    {
        var key = typeof(TEntity).Name;
        var adapter = (IObjectContextAdapter)EntityModelDataProvider.Database;
        var objectContext = adapter.ObjectContext;

        var container = objectContext.MetadataWorkspace.GetEntityContainer(
                                        objectContext.DefaultContainerName, System.Data.Entity.Core.Metadata.Edm.DataSpace.CSpace);
        var name = container.BaseEntitySets.Where((s) => s.ElementType.Name.Equals(key)).FirstOrDefault().Name;
        string sqlQuery = String.Format(@"SELECT VALUE entity FROM [VW_{0}] WHERE entity.{1} = @foreignkey", name, foreignKey);
        var query = objectContext.CreateQuery<TEntity>(sqlQuery, new ObjectParameter("foreignkey", valueFK));

        var tmpResult = query.ToList();
    }
    catch (Exception)
    {

        throw;
    }
}

And the error:

'entity.Average_FK' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near member access expression, line 1, column 58.

Generated query:

SELECT VALUE entity FROM [VW_Average_Client] WHERE entity.Average_FK = @foreignkey

I checked the Properties of the Entity and I do have one called "Average_FK".

Any idea how to achieve my goal?

UPDATE

I am trying to implement a generic where like this:

Expression<Func<TEntity, bool>> customFilter = entity => ComparePropertyValue<TEntity>(entity, foreignKey, valueFK);

var query = objectContext.CreateQuery<TEntity>("[" + name + "]").Where(customFilter);

....

private static bool ComparePropertyValue<TEntity>(TEntity entity, string property, object value)
{
    try
    {
        PropertyInfo propertyInfo = typeof(TEntity).GetProperty(property);
        return propertyInfo.GetValue(entity) == value;
    }
    catch (Exception)
    {

        throw;
    }
}

And this exception:

LINQ to Entities does not recognize the method 'Boolean ComparePropertyValue[VW_Average_Client](XXX.EntityModel.VW_Average_Client, System.String, System.Object)' method, and this method cannot be translated into a store expression.

It is considered a bad practice to hard code queries like that, and especially when the parameters aren't wrapped in DbParameters. Instead, you should use the generic Where LINQ expression which allows you to dynamically generate filters. Use your DbContext class to access the DbSet (these can be views) and pass your where clause and you're done. — hbulens, Jul 04 '16 at 14:02
could you please indicate me how to create a generic where clause I can set into the `.Where(XXXX)` ? — blfuentes, Jul 04 '16 at 18:34
I'd say this is a good place to start: http://www.asp.net/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application. You'll have to scroll down a bit to see it in action but don't forget to follow the rest as it will guide you through the ins and outs of the repository pattern in Entity Framework. — hbulens, Jul 04 '16 at 18:57
@hbulens I already make use of the repository pattern but I have a specific situation where I need to create a very generic function to filter a query on a runtime defined property — blfuentes, Jul 05 '16 at 07:53
In that case, I think expressions are the way to go: https://www.simple-talk.com/dotnet/.net-framework/giving-clarity-to-linq-queries-by-extending-expressions/. — hbulens, Jul 05 '16 at 08:14

score 4 · Accepted Answer · answered Jul 05 '16 at 08:38

Finally I found a solution.

(In case someone wants to post a better/cleaner answer I will set yours as the valid one)

public static void InvalidateDataGeneric<TEntity>(string foreignKey, int valueFK)
{
    try
    {
        var key = typeof(TEntity).Name;
        var adapter = (IObjectContextAdapter)EntityModelDataProvider.Database;
        var objectContext = adapter.ObjectContext;

        var container = objectContext.MetadataWorkspace.GetEntityContainer(
                                        objectContext.DefaultContainerName, System.Data.Entity.Core.Metadata.Edm.DataSpace.CSpace);
        var name = container.BaseEntitySets.Where((s) => s.ElementType.Name.Equals(key)).FirstOrDefault().Name;

        Func<TEntity, bool> filter = algo => ComparePropertyValue<TEntity>(algo, foreignKey, valueFK);

        var query = objectContext.CreateQuery<TEntity>("[" + name + "]").Where(filter);

        foreach (var element in query)
        {
            // whatever
        }

        objectContext.SaveChanges();
    }
    catch (Exception)
    {

        throw;
    }
}

private static bool ComparePropertyValue<TEntity>(TEntity entity, string property, object value)
{
    try
    {
        PropertyInfo propertyInfo = typeof(TEntity).GetProperty(property);
        return propertyInfo.GetValue(entity).Equals(value);
    }
    catch (Exception)
    {

        throw;
    }
}

score 0 · Answer 2 · answered Jul 04 '16 at 14:12

0

The place where entity is used is wrong, it should be:

string sqlQuery = String.Format(@"SELECT VALUE FROM [VW_{0}] entity WHERE entity.{1} = @foreignkey", name, foreignKey);

or even omit entity:

string sqlQuery = String.Format(@"SELECT VALUE FROM [VW_{0}] WHERE {1} = @foreignkey", name, foreignKey);

(note - I agree with the comment above that for several reasons this is not a recommended approach; there are better & cleaner solutions to achieve the same. But that would not answer the question...)

answered Jul 04 '16 at 14:12

Peter B

22,460
5
32
69

That way it raises another syntax exception before `FROM`. If you know a better and cleaner solution, please post it and I'll accept it as answer. I asked to find a solution for a generic query with dynamic where. – blfuentes Jul 04 '16 at 18:31
Could it then be that some/all of the tables or views `VW_...` do not have a column named `VALUE`? – Peter B Jul 05 '16 at 12:26

Generic query in EntityFramework with 'where' statement

2 Answers2

Linked