CODEWITHSUNDEEP
c#.netlambda.net-4.5
lemunk
lemunk

Reputation: 2636

Lambda not equal on join

Table 1 called Category contains 70 records Table 2 called FilterCategorys contains 0 records (currently).

my lambda join, I want to pull only records that don't match, so in this case I expect to get 70 records back. Here's my incorrect Lambda:

var filteredList = categorys
            .Join(filterCategorys,
                x => x.Id,
                y => y.CategoryId,
                (x, y) => new { catgeory = x, filter = y })
            .Where(xy => xy.catgeory.Id != xy.filter.CategoryId)
            .Select(xy => new Category()
                        {
                            Name = xy.catgeory.Name,
                            Id = xy.catgeory.Id,
                            ParentCategoryId = xy.catgeory.ParentCategoryId
                        })
            .ToList();

Whats the correct syntax I need here?

Upvotes: 1

Views: 9166

Answers (5)

Andrey Shendrik
Andrey Shendrik

Reputation: 1

Could you draw bracket and it should work. ....Where(xy => (xy.catgeory.Id != xy.filter.CategoryId))

Upvotes: 0

Wyatt Earp
Wyatt Earp

Reputation: 1823

Not sure if you have a requirement of using lambdas (rather than query syntax), but I prefer query syntax for statements that have outer joins.

This should be equivalent:

var filteredList = (
    from c in Categorys 
    join fc in FilterCategorys on c.Id equals fc.CategoryId into outer
    from o in outer.DefaultIfEmpty()
    select new 
    {
        Category = new Category 
        {
            Name = c.Name, 
            Id = c.Id, 
            ParentCategoryId = c.ParentCategoryId
        },
        Exists = (o != null)
    })
    .Where(c => !c.Exists)
    .Select(c => c.Category);

Upvotes: 3

lemunk
lemunk

Reputation: 2636

Well I came up with a solution that takes away the need for the join.

var currentIds = filterCategorys.Select(x => x.Id).ToList();
var filteredList = categorys.Where(x => !currentIds.Contains(x.Id));

very similar to @Zoff Dino answer, not sure about performance, maybe someone would like to check.

Upvotes: 1

Code Different
Code Different

Reputation: 93181

If you want to do it in purely lambda:

var match = categorys.Join(filterCategorys, x => x.Id, y => y.CategoryId, (x, y) => new { Id = x.Id });
var filteredList = categorys.Where(x => !match.Contains(new {Id = x.Id}));

I haven't measured the performance of this, but for 70 records, optimization is not an issue.

Upvotes: 2

George Vovos
George Vovos

Reputation: 7618

Try this:

var categories= ...
var filteredCategories=...
var allExceptFiltered = categories.Except(filteredCategories, new CategoryComparer()).ToList();

If you don't provide a custom Comparer that framework has no way of knowing that 2 Category objects are the same(even if they have the same ID),it just thinks that they are different objects (it checks for reference equality )

so you must add this class to your project:

public class CategoryComparer: IEqualityComparer<Category>
{
    public bool Equals(Category x, Category y)
    {
        if (x == null && y == null)
            return true;

        if (x == null)
            return false;

        if (y == null)
            return false;

        return x.CategoryId.GetHashCode() == y.CategoryId.GetHashCode();

    }

    public int GetHashCode(Category obj)
    {
        return obj.CategoryId.GetHashCode();
    }
}

update

Also check out Wyatt Earp's answer,it is very useful to know how to do an outer join

update 2 Your problem is the Join method.
The Where clause is "called" after the join.so after you have joined the listed based on the ID you select those which have different IDs,that's why you get no resuts

Upvotes: 0

Related Questions