CODEWITHSUNDEEP
c#linqdatasetlinq-to-dataset
Jed
Jed

Reputation: 10877

How to filter a collection using LINQ to DataSet

I'm working with a DataSet that contains two tables that I am trying to get data out of via LINQ.

I'm struggling with figuring out the syntax of how to return records that meet a condition.

Example:

Here are the two tables:

FooBar Items

FooBar Data

This query joins the two tables (Item Z will be filtered out)

private void ParseFooBar()
{
 ....
 var fooBars = from item in fooBarItems 
      join data in fooBarData on item["FooBar_Id"] equals data["FooBar_Id"]
      where (new[] {"A","B","C"}).Contains(item["id"])
      select new
      {
      id = item["id"],
      description = item["description"],
      wat = data["wat"],
      foo = data["foo"]                 
      };
}

This is the collection that was derived from the above query.

FooBar Items joined with FooBar Data

Question: How do I return Foo items only?

Notice how the Foo items have in their first row/record a non-null value while the Bar items do not have a non-null value in the first row/record. Using the fact that all Foo items will have at least one non-null in the foo column and Bar items will never have a non-null value in the foo column, how can I update the above query so that the query only returns Foo items? Likewise, how can I update the query so that it only returns Bar items?

Upvotes: 2

Views: 1568

Answers (2)

k.m
k.m

Reputation: 31454

It depends where you want to filter unwanted nulls out. If at the joining level, you can slightly alter your query:

from item in fooBarItems 
join data in fooBarData on item["FooBar_Id"] equals data["FooBar_Id"]
where (new[] {"A","B","C"}).Contains(item["id"]) && data["foo"] != null
select new
{
   id = item["id"],
   description = item["description"],
   wat = data["wat"],
   foo = data["foo"]                 
};

This will return only two elements:

  • A, I am a Foo, null, 10834
  • B, I am a Foo, null, 08385383

To get remaining ones (including those where foo is null yet they belong to Foo which has a non-null entry), use combination of .GroupBy and .Where after your main query:

fooBars
    .GroupBy(f => f.id)
    .Where(g => g.Any(f => f.foo != null))
    .SelectMany(g => g); // just to make it nice list-like looking

This will select entire groups, but only those which have at least one foo with non-null value (which would be all entries from final collection, except those with id = C).

Upvotes: 1

StriplingWarrior
StriplingWarrior

Reputation: 156469

It's not entirely clear to me what the criteria are for being a Foo versus a Bar, but here are a few ideas:

var foos1 = fooBars.Where(fb => fb.wat != "bar");
var foos2 = fooBars.Where(fb => fb.wat == "foo" || fb.wat == null);
var foos3 = fooBars.Where(fb => fb.wat == "foo" || fb.foo != null);
var bars = fooBars.Where(fb => fb.wat == "bar" || fb.foo != null);

Upvotes: 1

Related Questions