CODEWITHSUNDEEP
c#linqentity-framework-core
David Létourneau
David Létourneau

Reputation: 1250

How to filter a sublist inside parent and return parent with sublist filtered

I want to create a linq to sql query that will return a list of objects with a sublist that has been filtered.

It sounds easy but I'm not sure how to make this to work

Here the SQL Query which returns what I want: 

select * from Texts t inner join Translations tt on t.TranslationId = tt.Id 
inner join Pages p on tt.Id = p.TranslationId and tt.NeutralText = p.TitleNeutralTextId
where t.LanguageId = 1

Now I have to write this with linq.

What I've done so far is:

var query = this.Queryable() // Page entity
                .AsNoTracking()
                .Include(x => x.TitleTranslation.Texts);

            return (from m in query
                    from l in m.TitleTranslation.Texts
                    where m.TitleTranslation.Texts.Any(l => l.LanguageId == 1)
                    select m);

But it didn't work because I got the sublist with all languages instead of language with id #1 only.

Thanks for helping,

David

Upvotes: 0

Views: 1236

Answers (3)

David Létourneau
David Létourneau

Reputation: 1250

I found the solution I wanted thanks to Hasnain Bukhari. The solution was to start from the text table, assign the filter, include the desired Entity (Page) and put the results into memory (ToList()). Then select pages. It will give the result I want in the order I have to.

var query = textService.Queryable()
               .AsNoTracking()
               .Include(x => x.Translation.Pages)
               .Where(x => x.LanguageId == languageId).ToList();

return query.SelectMany(x => x.Translation.Pages);

Upvotes: 1

Hasnain Bukhari
Hasnain Bukhari

Reputation: 468

I think you must try lime this. this will work for you . This will be similar to sql query

One way to do this .

var result = from m in Texts
join Translations on Texts.TranslationId = Translation.Id
Join Pages on Translations.NeutralText = Pages.NeutralText
where Texts.LanguageId = 1
select m

There an other way to do this using entity framework 

var result =       
this.Queryable().AsNoTracking().Include(x=>x.Translations).Where(x=>x.LanguageId= 1)

Upvotes: 2

user1672994
user1672994

Reputation: 10839

Any specific reason you are writing query? Either you can use Eager Loading of EF to load all the child tables, Or below Linq statement can fetch the required result

    var result = texts.Join(translations, t => t.TranslationId, tt => tt.Id, (t, tt) => new {t, tt})
        .Join(pages, ttt => new { Id = ttt.tt.Id, NeutralTextId = ttt.tt.NeutralText }, p => new { Id = p.TranslationId, NeutralTextId = p.TitleNeutralTextId }, (ttt, p) => new {ttt, p})
        .Where(tttt => tttt.ttt.t.LanguageId == 1);

Here replace texts, translations and pages with actual dbContext entities collection property.

Upvotes: 2

Related Questions