How to filter child collections in Linq

Question

I need to filter the children of an object in linq using a single linq query. Is it possible?

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

Now if I have the following linq request

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

I get a set 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 this child list of Verse Translations.

So far, the only way I've been able to come up with is to use a new type of Anonymous or otherwise. Properly

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
                   };

, . , linq, .

, MAC

Answer 1

, , .

        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;

.

.

Answer 2

, .

Load Include of VerseTranslations Where.

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

Verse VerseTranslations. :

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

Answer 3

,

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

"" 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 4

, linq, ?

, - . , (Verse, VerseTranslation), - "". myType :

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);

Verse Translations . , (, ). , "" , , .

LINQ, , . , : Verse . , SingleOrDefault ( FirstOrDefault), , SelectMany :

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 };

Verse , "" Verse/Translation. DefaultIfEmpty() , , , .