Entity Framework Core 6, select with interchangable Where clause

Question

I'm converting some inline SQL code over to Entity Framework Core 6. The overall application is in C#. Some of the existing code has SQL that can take an optional WHERE clause.

A simplified example might look like this:

public List<DataObject> SelectWithWhere(string optionalWhere)
{    
    string sql = string.Format("SELECT * FROM SomeTable {0} ;", optionalWhere);
   
    // call to DB class to execute query and format into List (pseudo code)
    List<DataObject> list = ExecuteSqlAndFormat(sql);
 
    return list;
}

I'd like to be able to pass in Where criteria to a similar function that uses Entity Framework Core 6, but have not been able to find any good clear examples of passing a where clause, or some kind of entity structure that represents a where clause, into a method that can be then passed to EF Core.

public List<DataObject> SelectWithWhere(string optionalWhere)
{                
    List<DataObject> list = (from t in dbContext.SomeTable.Where(-- what to do here? --)
                             select new DataObject
                             {
                                  // fill in data members from query
                             }
                             ).ToList<DataObject>()
     
    return list;
}

The preferred solution would use only what is available in Entity Framework Core 6 and not any third party add-ons or proprietary tools.

Thanks!

use expressions instead of strings, take a look at this answer https://stackoverflow.com/questions/62258983/how-to-pass-a-runtime-parameter-to-an-ef-core-predicate-expression — Jeff, Jul 17 '23 at 20:37
Thanks Jeff, using your suggested link I was able to get the functionality I needed. I posted a sample of some working code that illustrates what I want to do. — Ken, Jul 18 '23 at 15:59

score 1 · Answer 1 · answered Jul 17 '23 at 21:04

1

NOTE: Below optionalWhere is a search value

public List<DataObject> SelectWithWhere(string optionalWhere)
{                
    List<DataObject> list = (from t in dbContext.SomeTable
        Where(t.SomeFieldName.Contains(optionalWhere)
        || t.SomeOtherFieldName.Contains(optionalWhere)
        select new DataObject
        {
            // fill in data members from query
        }
        ).ToList<DataObject>()
 
        return list;
    }

answered Jul 17 '23 at 21:04

Doug

69
1
1
11

Thanks, but the where clause is not a search string. It would be something similar to "WHERE = AND IS NOT " - that sort of where clause with one or more conditions on different fields and different data types. – Ken Jul 18 '23 at 13:17

score 0 · Answer 2 · answered Jul 18 '23 at 16:13

Thanks to the link proved by @Jeff, I was able to put together a solution that will be incorporated into my code. A sample solution is below.

First, assume that Entity Framework Core 6 is installed and has generated the classes and repositories for a DB.

To test, just pass one of the Where Expressions into the WhereTestMethod().

// Method to test the dynamic Where clauses.
public string TestDynamicWhere()
{
    string retval = string.Empty;
    List<ResultListClass> resultListClass = null;
    int isActiveFilter = 1;
    string graded = "Graded";

    try
    {
        // three different where conditions to test:
        
        // no external variables:
        Expression<Func<EF_ClassName, bool>> WhereTestFx = (EF_ClassName c) => c.isActive == true;
    
       // one external variable:
       Expression<Func<EF_ClassName, bool>> WhereTestFx2 = (EF_ClassName c) => c.isActive == Convert.ToBoolean(isActiveFilter);
    
       // two external variables:
       Expression<Func<EF_ClassName, bool>> WhereTestFx3 = (EF_ClassName c) => c.isActive == Convert.ToBoolean(isActiveFilter) &&
                                                                               c.NameField.Contains(graded);

        resultListClass = WhereTestMethod(WhereTestFx3);

        retval = JsonConvert.SerializeObject(resultListClass, Newtonsoft.Json.Formatting.Indented);
    }
    catch (Exception ex)
    {
        retval = ex.Message;
    }

    return retval;

}

// non repository class to use for test results for this example
public class ResultListClass
{
    public string NameField { get; set; }
    public bool isActive { get; set; }
}

// method that takes a Where Func, executes the query, and returns a result
public List<ResultListClass> WhereTestMethod(Expression<Func<EF_ClassName, bool>> fx)
{
    List<ResultListClass> resultListClass = null;

    try
    {
        resultListClass = (from a in dbContext.EF_ClassName.Where(fx)
                           select (new ResultListClass
                           {
                              isActive = a.isActive,
                              NameField = a.NameField,
                           }
                           )).ToList<ResultListClass>();
    }
    catch(Exception ex) 
    {
        throw;
    }
    
    return resultListClass;
}

Entity Framework Core 6, select with interchangable Where clause

2 Answers2