How to filter child collections in Linq.

Question

Hi!

I need to filter the child elements of an entity in linq using a single linq query. Is this possible?

Suppose I have two related tables. Verses and VerseTranslations. The entity created by LINQ to SQL is such that i have a Verse object that contains a child object that is a collection of VerseTranslation.

Now if i have the follow linq query

var res = from v in dc.Verses
                  where v.id = 1
                  select v;

I get a collection of Verses whose id is 1 and each verse object contains all the child objects from VerseTranslations.

What I also want to do is filter that child list of Verse Translations.

So far the only way i have been able to come up with is by using a new Type Anonymous or otherwise. As follows

var res= from v in dc.Verses
                   select new myType
                   {
                       VerseId = v.VerseId,
                       VText = v.Text,
                       VerseTranslations = (from trans in v.VerseTranslations
                                               where languageId==trans.LanguageId
                                               select trans
                   };

The above code works, but i had to declare a new class for it. Is there no way to do it in such a manner such that the filtering on the child table can be incorporated in the first linq query so that no new classes have to be declared.

Regards, MAC

Answer 1

If this is coming from a database, you could run your first statement.

Then do a Load or Include of VerseTranslations with a Where clause.

http://msdn.microsoft.com/en-us/library/bb896249.aspx

Do you have a relationship in your model between Verse and VerseTranslations. In that case this might work:

var res= from v in 
dc.Verses.Include("VerseTranslations").Where(o => languageId==o.LanguageId)
select v;

Answer 2

Filtering on the enclosed collection of the object,

var res = dc.Verses
            .Update(v => v.VerseTranslations 
                      =  v.VerseTranslations
                          .Where(n => n.LanguageId == languageId));

By using extension method "Update" from HookedOnLinq

public static class UpdateExtensions {

    public delegate void Func<TArg0>(TArg0 element);

    /// <summary>
    /// Executes an Update statement block on all elements in an IEnumerable<T> sequence.
    /// </summary>
    /// <typeparam name="TSource">The source element type.</typeparam>
    /// <param name="source">The source sequence.</param>
    /// <param name="update">The update statement to execute for each element.</param>
    /// <returns>The numer of records affected.</returns>
    public static int Update<TSource>(this IEnumerable<TSource> source, Func<TSource> update) {
        if (source == null) throw new ArgumentNullException("source");
        if (update == null) throw new ArgumentNullException("update");
        if (typeof(TSource).IsValueType) 
            throw new NotSupportedException("value type elements are not supported by update.");

        int count = 0;
        foreach(TSource element in source) {
            update(element);
            count++;
        }
        return count;
    }
}

Answer 3

Is there no way to do it in such a manner such that the filtering on the child table can be incorporated in the first linq query so that no new classes have to be declared?

Technically, the answer is no. If you're trying to return more data than a single entity object (Verse, VerseTranslation) can hold, you'll need some sort of object to "project" into. However, you can get around explicitly declaring myType by using an anonymous type:

var res = from v in dc.Verses
          select new
          {
              Verse = v,
              Translations = (from trans in v.VerseTranslations
                              where languageId==trans.LanguageId
                              select trans).ToList()
          };

var first = res.First();
Console.WriteLine("Verse {0} has {1} translation(s) in language {2}.",
    first.Verse.VerseId, first.Translations.Count, languageId);

The compiler will generate a class with appropriately-typed Verse and Translations properties for you. You can use these objects for just about anything as long as you don't need to refer to the type by name (to return from a named method, for example). So while you're not technically "declaring" a type, you're still using a new type that will be generated per your specification.

As far as using a single LINQ query, it all depends how you want the data structured. To me it seems like your original query makes the most sense: pair each Verse with a filtered list of translations. If you expect only a single translation per language, you could use SingleOrDefault (or FirstOrDefault) to flatten your subquery, or just use a SelectMany like this:

var res= from v in dc.Verses
         from t in v.VerseTranslations.DefaultIfEmpty()
         where t == null || languageId == t.LanguageId
         select new { Verse = v, Translation = t };

If a Verse has multiple translations, this will return a "row" for each Verse/Translation pair. I use DefaultIfEmpty() as a left join to make sure we will get all Verses even if they're missing a translation.

Answer 4

So i finally got it to work thanks to the pointers given by Shiraz.

        DataLoadOptions options = new DataLoadOptions();
        options.AssociateWith<Verse>(item => item.VerseTranslation.Where(t => languageId.Contains(t.LanguageId)));

        dc.LoadOptions = options;

        var res = from s in dc.Verse
                   select s;

This does not require projection or using new extension classes.

Thanks for all your input people.