Dynamic Predicate with LINQ

Posts   
 
    
egutierrez
User
Posts: 7
Joined: 12-Nov-2018
# Posted on: 11-Apr-2019 02:42:12   

SD.LLBLGen.Pro.ORMSupportClasses: 4.2.20160929 SD.LLBLGen.Pro.DQE.SqlServer: 4.2.20160929 .NET Framework 4.6.1 Sql Server 12.0.5207.0 (2012)

Hi,

I'm trying to create a dynamic linq query but I have some problems create the "where" predicates because I have to use Joins and other linq methods..

I've already read this posts: https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14224 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14144

In this post a PredicateBuilder class is used but with a know entity type, in my case because I'm using a more complex query, it results in some anonymous types so I don't know how to use the PredicateBuilder in that case.

Usually we should start with the PredicateBuilder like this: var predicate = PredicateBuilder.Null<PrimaryKeyValueEntity>();

But because after the Join there is an anonymous type, I don't know how to use it.

What I have to do is to dynamically create the Where predicates.

This is the query, sorry if has some 'temp' names and so on, this is not very clean at the moment.


var query2 =
            linqMetaData.PrimaryKeyValue
            .Join(
               linqMetaData.PrimaryKey,
               primaryKeyValue2 =>
                  new
                  {
                      Id = primaryKeyValue2.PrimaryKeyId,
                      EnvironmentId = sourceEnvironmentId
                  },
               primaryKey2 =>
                  new
                  {
                      primaryKey2.Id,
                      primaryKey2.EnvironmentId
                  },
               (primaryKeyValue2, primaryKey2) =>
                  new
                  {
                      primaryKeyValue2,
                      primaryKey2
                  }
            )
            .Join(
               linqMetaData.PrimaryKeySyncGroup,
               temp0 => temp0.primaryKey2.SyncGroupId,
               syncGroup2 => syncGroup2.Id,
               (temp0, syncGroup2) =>
                  new
                  {
                      temp0,
                      syncGroup2
                  }
            )
                .Where(
                   temp1 =>
                         ((((temp1.temp0.primaryKeyValue2.MappedColumnId == 1895) &&
                                  (temp1.temp0.primaryKeyValue2.Value == "303867")
                               ) ||
                                  ((temp1.temp0.primaryKeyValue2.MappedColumnId == 2568) &&
                                     (temp1.temp0.primaryKeyValue2.Value == "1016")
                                  )
                            ) ||
                               ((temp1.temp0.primaryKeyValue2.MappedColumnId == 72) &&
                                  (temp1.temp0.primaryKeyValue2.Value == "1")
                               )
                         )
                )
             .GroupBy(
               temp1 =>
                  new
                  {
                      PrimaryKeyId = temp1.temp0.primaryKeyValue2.PrimaryKeyId,
                      MappedTableId = temp1.syncGroup2.MappedTableId
                  },
               temp1 =>
                  new
                  {
                      primaryKeyValue2 = temp1.temp0.primaryKeyValue2,
                      primaryKey2 = temp1.temp0.primaryKey2
                  }
            )
            .Where(
               primaryKeys =>
                     (((primaryKeys.Count() == 2) && (primaryKeys.Key.MappedTableId == 15)) ||
                        ((primaryKeys.Count() == 1) && (primaryKeys.Key.MappedTableId == 1))
                     )
            )
            .Select(
               primaryKeys =>
                  new
                  {
                      PrimaryKeyId = primaryKeys.Key.PrimaryKeyId,
                      PrimaryKeyCount = primaryKeys.Count(),
                      MappedTableId = primaryKeys.Key.MappedTableId
                  }
            );

In that code I have to replace the where with a dynamic predicate:


  .Where(
                 temp1 =>
                         ((((temp1.temp0.primaryKeyValue2.MappedColumnId == 1895) &&
                                 (temp1.temp0.primaryKeyValue2.Value == "303867")
                             ) ||
                                 ((temp1.temp0.primaryKeyValue2.MappedColumnId == 2568) &&
                                     (temp1.temp0.primaryKeyValue2.Value == "1016")
                                 )
                            ) ||
                             ((temp1.temp0.primaryKeyValue2.MappedColumnId == 72) &&
                                 (temp1.temp0.primaryKeyValue2.Value == "1")
                             )
                         )
                )

Thanks,

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 11-Apr-2019 07:59:35   

Hi there,

PredicateBuilder is a good choice. However for anonymous types it won't help you. My recommendation after doing a lot of things very similar to yours, is: create a Database View, then map it to a TypedView or even to an Entity in LLBLGen Designer. That way you have more control over the joins, the groups, and the filter is more clear in the .net code.

Once you work with this new entity based on DB View, you could use PredicateBuilder with LINQ2LLBL. Another options are: work with QuerySpec, or even LLBLGen API. In fact this last one (LLBLGen API) is more low-level and verbose but it lets you do things to have more control over those details. For example, for this kind of queries see Working with DynamicLists

David Elizondo | LLBLGen Support Team