CODEWITHSUNDEEP
entity-frameworklinqjoinexpression
Dan K.
Dan K.

Reputation: 21

How to do Entity Framework/Linq join in query with where clause expressions

I have a Entity Framework query where the where clauses' change based on several selections.
Here is my code:

Expression<Func<CtCsGroup, bool>> where_expression1 = x => 1 == 1;
Expression<Func<CtCsGroup, bool>> where_expression2 = x => 1 == 1;
Expression<Func<CtCsGroup, bool>> where_expression3 = x => 1 == 1;


if (selections.SearchCSGroup.NoNull() != "")
{
    where_expression1 = x => x.CsGroup.ToUpper() == selections.SearchCSGroup.ToUpper();
}

if (selections.SearchUserId.NoNull() != "")
{
    where_expression3 = x => x.UsrIdn.ToUpper() == selections.SearchUserId.ToUpper();
}

if (!selections.SearchIncludeRetires)
{
    where_expression2 = x => x.CsRetire != "Y";
}

var groupDTOs = (from g in _infobaseContext.CtCsGroup
                 .Where(where_expression1)
                 .Where(where_expression2)
                 .Where(where_expression3)
                 select new
                 {
                     UsrIdn = g.UsrIdn,
                     CsGroup = g.CsGroup,
                     CsRetire = g.CsRetire,
                     CsGroupName = "test" // n.CsGroupName
                 }).ToList();

I want to add a join to another table.

How do I add this to the above query? I have tried several different formats but whenever I add it, I get an error "(where_expression1)" ,"(where_expression3)", etc.

Thanks.

Here are some more details...

var groupDTOs = (from g in _infobaseContext.CtCsGroup
                         join n in _infobaseContext.TblCsGroupList
                                    on g.CsGroup equals n.CsGroup
                         select new
                         {
                             UsrIdn = g.UsrIdn,
                             CsGroup = g.CsGroup,
                             CsRetire = g.CsRetire,
                             CsGroupName =  n.CsGroupName
                         }).ToList();


var groupDTOs = (from g in _infobaseContext.CtCsGroup
                         .Where(where_expression1)
                         select new
                         {
                             UsrIdn = g.UsrIdn,
                             CsGroup = g.CsGroup,
                             CsRetire = g.CsRetire,
                             CsGroupName =  "test"  //n.CsGroupName
                         }).ToList();


var groupDTOs = (from g in _infobaseContext.CtCsGroup
                         join n in _infobaseContext.TblCsGroupList
                                    on g.CsGroup equals n.CsGroup
                         .Where(where_expression1)
                         select new
                         {
                             UsrIdn = g.UsrIdn,
                             CsGroup = g.CsGroup,
                             CsRetire = g.CsRetire,
                             CsGroupName =  n.CsGroupName
                         }).ToList();

Upvotes: 1

Views: 1034

Answers (1)

Chris Schaller
Chris Schaller

Reputation: 16554

Generally we would strive to compose the query over multiple steps, rather than trying to inject multiple pre-composed predicates. You are already using Fluent notation to apply your predicates, so split that process out from your Query based expression.

You will also notice that the comparison values have been parameterised rather than letting the linq interpreter decide what is a query reference and what is a discrete or scalar value for us.

You haven't posted the specific errors, however where the linq expression is being evaluated it the elections.SearchCSGroup is probably out of scope and cannot be resolved. Deliberate parameterization avoids this confusion.

IQueryable<CtCsGroup> groupQuery = _infobaseContext.CtCsGroup;
if (selections.SearchCSGroup.NoNull() != "")
{
    // Force the comparison against a discrete parameter value
    var selection = selections.SearchCSGroup.ToUpper();
    groupQuery = groupQuery.Where(x => x.CsGroup.ToUpper() == selection);
}

if (selections.SearchUserId.NoNull() != "")
{
    var selection = selections.SearchUserId.ToUpper();
    groupQuery = groupQuery.Where(x => x.UsrIdn.ToUpper() == selection);
}

if (!selections.SearchIncludeRetires)
{
    // NOTE: "Y" will be correctly interpreted as a discrete parameter value
    groupQuery = groupQuery.Where(x => x.CsRetire != "Y");
}

var groupDTOs = (from g in groupQuery 
                 select new
                 {
                     UsrIdn = g.UsrIdn,
                     CsGroup = g.CsGroup,
                     CsRetire = g.CsRetire,
                     CsGroupName = "test" // n.CsGroupName
                 }).ToList();

That last section could also be composed as a fluent expression, rather than a query based expression:

var groupDTOs = groupQuery.Select(g => new
                {
                    UsrIdn = g.UsrIdn,
                    CsGroup = g.CsGroup,
                    CsRetire = g.CsRetire,
                    CsGroupName = "test" // n.CsGroupName
                }).ToList();

This pattern helps you avoid these issues cause by trying to pass through pre-compiled predicates in the first place, especially it removes the possibility of defining the predicates with the incorrect type bindings.

This pattern is also helpful for composing expressions that have conditional sort columns or directions.

The final issue you have posted is a red herring, you have mixed up the syntax, that expression was never meant to work.

Visual Studio is giving this error: Argument 2: cannont convert from 'System.Linq.Expressions<System.Func<IBDataServices.Models.CtCsGroup, bool>>' to 'System.Func<char,bool>'

Specifically this section:

join n in _infobaseContext.TblCsGroupList
          on g.CsGroup equals n.CsGroup.Where(where_expression1)

You probably see it now that the line break is removed, n.CsGroup is a string, and it itself IEnumerable<char> so it is assuming you want to apply the where_expression1 predicate to the value of n.CsGroup

We can still join onto the composed query using the normal linq join expressions:

var groupDTOs = (from g in groupQuery
                 join n in _infobaseContext.TblCsGroupList
                        on g.CsGroup equals n.CsGroup 
                 select new
                 {
                     UsrIdn = g.UsrIdn,
                     CsGroup = g.CsGroup,
                     CsRetire = g.CsRetire,
                     CsGroupName = n.CsGroupName
                 }).ToList();

If your schema has a navigation property on CtCsGroup called CsGroup that represents the relationship between TblCsGroupList and CtCsGroup then we can use dot notation and the necessary joins will be implicit.

NOTE: This makes broad assumptions about the schema and is only included to show a common structural alternative to manually defining joins

var groupDTOs = (from g in groupQuery
                 select new
                 {
                     UsrIdn = g.UsrIdn,
                     CsGroup = g.CsGroup,
                     CsRetire = g.CsRetire,
                     CsGroupName = g.CsGroup.CsGroupName
                 }).ToList();

Upvotes: 1

Related Questions