CODEWITHSUNDEEP
c#linq
Adam Cox
Adam Cox

Reputation: 3661

C# LINQ join 2 tables with where clause

I am trying to join 2 tables while filtering with where clause. The data looks like this:

Category
Name Selected
A    0
B    1
C    0

SubCategory
Name ParentCategory Selected
s1   A              0
s2   B              1
s3   B              0

Expected results:

ParentCatName SubCatName SubCatSelected
B             s2         1
B             s3         0

Actual results:

ParentCatName SubCatName SubCatSelected
B             s2         1
B             s3         1  <-- should be 0

The code I am using is this:

IEnumerable<Category> result =
    from s in subcategories
    join c in categories
    on s.Parent equals c.Name into t
    from r in t.DefaultIfEmpty()
    where r == null ? false : r.Selected == true
    select new Category
    {
        Name = s.Name,
        Parent = (r == null ? string.Empty : r.Name),
        Selected = r.Selected
    };

EDIT: Something that helped me get clarity was to temporarily rewrite this to see the resulting data structures...

var result =
    from s in subcategories
    join c in categories
    on s.Parent equals c.Name into t
    from r in t.DefaultIfEmpty()
    select new
    {
        s, r
    };

Then I came up with the answer to the filtering of selected categories. See my answer below..

Upvotes: 1

Views: 5932

Answers (4)

maxshuty
maxshuty

Reputation: 10662

It looks like you're setting it wrong. If r == null then you're setting it to false, otherwise you're setting it to true here: r.Select == true.

Just by reading your query it looks like you may not need that where clause at all.

You probably want something like this:

IEnumerable<Category> result =
    from s in subcategories
    join c in categories
    on s.Parent equals c.Name into t
    from r in t.DefaultIfEmpty()
    select new Category
    {
        Name = s.Name,
        Parent = (r == null ? string.Empty : r.Name),
        Selected = r.Selected
    };

Or if you need to do the null check then do this:

IEnumerable<Category> result =
    from s in subcategories
    join c in categories
    on s.Parent equals c.Name into t
    from r in t.DefaultIfEmpty()
    where r != null //I added the null check here
    select new Category
    {
        Name = s.Name,
        Parent = (r.Name), //I removed the null check here
        Selected = r.Selected
    };

Upvotes: 1

Ivan Stoev
Ivan Stoev

Reputation: 205559

Don't overcomplicate the things. What you are trying to achieve is to filter subcategories by the selected categories. You can get the desired result with the following simple query

var result = from s in subcategories
             join c in categories on s.Parent equals c.Name
             where c.Selected
             select s;

Upvotes: 3

Adam Cox
Adam Cox

Reputation: 3661

Okay. So I did some more looking and came up with this...

IEnumerable<Category> result =
    from s in subcategories
    join c in categories.Where(f => f.Selected)
    on s.Parent equals c.Name into t
    from r in t.DefaultIfEmpty()
    where r == null ? false : true
    select new Category
    {
        Name = s.Name,
        Parent = s.Name,
        Selected = s.Selected,
    };

To filter join to only selected parent category, I have added lambda expression right to that data.

Upvotes: 0

Alper Tunga Arslan
Alper Tunga Arslan

Reputation: 579

I think, your t value inculudes categoies list. t must be include subcategory list and then you can take selected value of subcategory. So you always get selected value as 1 pls try this:

IEnumerable<SubCategory> result =
    from c in categories
    join s in subcategories
    on c.Name equals s.Parent into t
    from r in t.DefaultIfEmpty()
    where r == null ? false : r.Selected == true
    select new SubCategory
    {
        Name = s.Name,
        Parent = (r == null ? string.Empty : r.Name),
        Selected = r.Selected
    };

OBS:I'm not try this now. But I think works.

Upvotes: 0

Related Questions