Establish a Link Between Two Lists in Linq to Entities Where Clause

Establish a link between two lists in linq to entities where clause

I'm pretty new to Linq and EF

You are out of luck, because "this simple thing" is relatively easy in LINQ to Objects, but quite hard (almost impossible) in LINQ to Entities.

In order to somehow solve it, you need to build manually LINQ to Entities compatible Expression.

First, you'll need some helpers for building expression predicates. PredicateBuilder is a popular choice, but it does not produce EF compatible expressions and requires LinqKit and AsExpandable inside the repository. So I use the below helpers which are similar, but produce final compatible expressions:

public static class PredicateUtils
{
    sealed class Predicate<T>
    {
        public static readonly Expression<Func<T, bool>> True = item => true;
        public static readonly Expression<Func<T, bool>> False = item => false;
    }
    public static Expression<Func<T, bool>> Null<T>() { return null; }
    public static Expression<Func<T, bool>> True<T>() { return Predicate<T>.True; }
    public static Expression<Func<T, bool>> False<T>() { return Predicate<T>.False; }
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
    {
        if (Equals(left, right)) return left;
        if (left == null || Equals(left, True<T>())) return right;
        if (right == null || Equals(right, True<T>())) return left;
        if (Equals(left, False<T>()) || Equals(right, False<T>())) return False<T>();
        var body = Expression.AndAlso(left.Body, right.Body.Replace(right.Parameters[0], left.Parameters[0]));
        return Expression.Lambda<Func<T, bool>>(body, left.Parameters);
    }
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
    {
        if (Equals(left, right)) return left;
        if (left == null || Equals(left, False<T>())) return right;
        if (right == null || Equals(right, False<T>())) return left;
        if (Equals(left, True<T>()) || Equals(right, True<T>())) return True<T>();
        var body = Expression.OrElse(left.Body, right.Body.Replace(right.Parameters[0], left.Parameters[0]));
        return Expression.Lambda<Func<T, bool>>(body, left.Parameters);
    }

    static Expression Replace(this Expression expression, Expression source, Expression target)
    {
        return new ExpressionReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ExpressionReplacer : ExpressionVisitor
    {
        public Expression Source;
        public Expression Target;
        public override Expression Visit(Expression node)
        {
            return node == Source ? Target : base.Visit(node);
        }
    }
}

Second, define a helper method in your controller for a single criteria like this

static Expression<Func<Person, bool>> AbilityFilter(int index, int value)
{
    return p => p.AllAbilities.OrderBy(a => a.Id).Skip(index).Take(1).Any(a => a.Value > value);
}

Finally, build the filter and pass it to GetAll method:

var filter = PredicateUtils.Null<Person>();
for (int i = 0; i < AbilitiesInput.Count; i++)
    filter = filter.And(AbilityFilter(i, AbilitiesInput[i]));
GetAll(filter);

The techniques used are definitely not for a novice, but I see no simple way to solve that particular problem.

Get Data From two Lists conditionally using LINQ Entity Framework

You could use a linq join clause as follows:

var ThirdList = 
    from member in MembersList
    join userInfo in UserInfoList on member.FKUserID equals userInfo.FKUserID
    where member.TeamID == 12  // change 12 to the TeamID needed
    select new { TeamID = member.TeamID, FKUserID = member.FKUserID, Name = userInfo.Name};

foreach(var tlist in ThirdList)
{
    Console.WriteLine("{0} | {1} | {2}", tlist.TeamID, tlist.FKUserID, tlist.Name);
}

Output:

12 | 6 | kjh
12 | 7 | ghg
12 | 8 | dfi

Intersection of two lists using LINQ

EDIT:
People in the comments are taking about overriding equals for your Company object and they are correct however we might be able to do something easier. The reason you need to override equals is because .Net doesn't know how to find equality in an object you created. so you would need to program in how to let it know. It does know how to find equality in an ID most times however.

EDIT 2:
The Intersect Any is the way to go because you are want to compare a list to a list

public List<User> GetUsers(User admin)
{
    var adminCompanyIDs = admin.Companys.Select(c => c.ID);
    return Users.Where(user=>user.Companys.Select(c => c.ID).Intersect(adminCompanyIDs).Any()).ToList();        
}

So Contains will search the list to see if any single value are in the list. Because it only searches for a single value it won't work for this.

Intersect will return the intersections of the two lists. for example [1,2,3] [2,3,4] would give [2,3].

Where requires a boolean for the function evaluation. Give me the values in my list where the function given returns true. So when you give back [2,3] it complains. Any says are there any results in the list. so [2,3].Any() returns true, satisfying the Where.

Contains doesn't return the Intersection of the list, just tells you True of False, Does the value exist

Hope that helps.

Linq pairing two Lists of different classes with join

Let's consider the following two classes and lists

class A { 
    public int Id {get;set;}
    public string Name {get;set;}
}

class B { 
    public int Id {get;set;}
    public decimal Size {get;set;}
}

(...) 

var la = new A[]{ new A { Id = 1, Name = "Snake"}, new A { Id = 2, Name = "Adam"}};
            
var lb = new B[]{ new B { Id = 1, Size = 0.8m}, new B { Id = 2, Size = 1}};

You can create an object with two properties:

var lab = from a in la
          join b in lb on a.Id equals b.Id
          select new {a, b}; // or select new { A = a, B = b};

I used anonymous type, but you can create a type the has two properties A and B and use that.

If you wish for a tuple, use a modern tuple with named fields:

          select (A: a, B: b);

Having said that, maybe an object with the properties that you need is the best choice.

var lab = from a in la
          join b in lb on a.Id equals b.Id
          select new  
          { 
             Id = a.Id, 
             Name = a.Name,
             Size = b.Size
          };

LINQ to Entities - where..in clause with multiple columns

How about:

var result = locations.Where(l => keys.Any(k => 
                    k.Country == l.Country && 
                    k.City == l.City && 
                    k.Address == l.Address));

UPDATE

Unfortunately EF throws NotSupportedException on that, which disqualifies this answer if you need the query to run on DB side.

UPDATE 2

Tried all kinds of joins using custom classes and Tuples - neither works. What data volumes are we talking about? If it's nothing too big, you could either process it client-side (convenient) or use unions (if not faster, at least less data is transmitted).

LINQ to entities - Building where clauses to test collections within a many to many relationship

After reading about the PredicateBuilder, reading all of the wonderful posts that people sent to me, posting on other sites, and then reading more on Combining Predicates and Canonical Function Mapping.. oh and I picked up a bit from Calling functions in LINQ queries (some of these classes were taken from these pages).

I FINALLY have a solution!!! Though there is a piece that is a bit hacked...

Let's get the hacked piece over with :(

I had to use reflector and copy the ExpressionVisitor class that is marked as internal. I then had to make some minor changes to it, to get it to work. I had to create two exceptions (because it was newing internal exceptions. I also had to change the ReadOnlyCollection() method's return from:

return sequence.ToReadOnlyCollection<Expression>();

To:

return sequence.AsReadOnly();

I would post the class, but it is quite large and I don't want to clutter this post any more than it's already going to be. I hope that in the future that class can be removed from my library and that Microsoft will make it public. Moving on...

I added a ParameterRebinder class:

public class ParameterRebinder : ExpressionVisitor {
        private readonly Dictionary<ParameterExpression, ParameterExpression> map;

        public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map) {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp) {
            return new ParameterRebinder(map).Visit(exp);
        }

        internal override Expression VisitParameter(ParameterExpression p) {
            ParameterExpression replacement;
            if (map.TryGetValue(p, out replacement)) {
                p = replacement;
            }
            return base.VisitParameter(p);
        }
    }

Then I added a ExpressionExtensions class:

public static class ExpressionExtensions {
        public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge) {
            // build parameter map (from parameters of second to parameters of first)
            var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);

            // replace parameters in the second lambda expression with parameters from the first
            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

            // apply composition of lambda expression bodies to parameters from the first expression 
            return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
        }

        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) {
            return first.Compose(second, Expression.And);
        }

        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) {
            return first.Compose(second, Expression.Or);
        }
    }

And the last class I added was PredicateBuilder:

public static class PredicateBuilder {
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

}

This is my result... I was able to execute this code and get back the resulting "content" entities that have matching "tag" entities from the tags that I was searching for!

    public static IList<Content> GetAllContentByTags(IList<Tag> tags) {
        IQueryable<Content> contentQuery = ...

        Expression<Func<Content, bool>> predicate = PredicateBuilder.False<Content>();

        foreach (Tag individualTag in tags) {
            Tag tagParameter = individualTag;
            predicate = predicate.Or(p => p.Tags.Any(tag => tag.Name.Equals(tagParameter.Name)));
        }

        IQueryable<Content> resultExpressions = contentQuery.Where(predicate);

        return resultExpressions.ToList();
    }

Please let me know if anyone needs help with this same thing, if you would like me to send you files for this, or just need more info.

How to write a LINQ to Entities query with List in a WHERE condition

Don't use a List<Ingredient> for the ingredients that you want to find; use a HashSet<Ingredient> and the IsProperSubsetOf method, which accepts a collection as its argument:

.Where(x => ingredients.IsProperSubsetOf(x.IngredientList))

In addition to being an O(n+m) operation, this has the added benefit of being code that tells you what it's doing when you look at it.

Edit

In case the above is not clear:

public List<Recipe> GetRecipesWhichHaveGivenIngredients(HashSet<Ingredient> ingredients)
{
   using (DataContext context = new DataContext())
   {
       return context.Recipes
           .Where(x => ingredients.IsProperSubsetOf(x.IngredientList)  
           .ToList();
   }
 }

how do I join two lists using linq or lambda expressions

It sounds like you want something like:

var query = from order in workOrders
            join plan in plans
                 on order.WorkOrderNumber equals plan.WorkOrderNumber
            select new
            {
                order.WorkOrderNumber,
                order.Description,
                plan.ScheduledDate
            };

Join two lists and select nested items with linq

Try this:

IEnumerable<string> queries = LandingSilo.Relationships
    .Where(r => r.RelationshipType == 1)
    .Join(
        LandingSilo.Nodes, 
        r => r.SlaveKey, 
        n => n.Key, 
        (r, n) => n.Queries.SingleOrDefault(q => q.Seq == r.QueryId))
    .Where(q => q != null)
    .Select(q => q.Query);

Line by line: filter all Relationships with type different from 1, join on SlaveKey/Key and select the only query in the node that has the Seq equal to the Relationships QueryId. Filter out null results and select the Query property. This is going to throw an InvalidOperationException if there are multiple queries within one node matching.

This can be also done in the LINQ keyword syntax like this:

IEnumerable<string> queries = 
    from r in LandingSilo.Relationships
    where r.RelationshipType == 1
    join n in LandingSilo.Nodes on r.SlaveKey equals n.Key
    from q in n.Queries.SingleOrDefault(q => q.Seq == r.QueryId)
    where q != null
    select q.Query;

---

Related Topics