CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworklinq
Anand
Anand

Reputation: 729

Efficient join in Entity Framework/LINQ

I have two SQL Server tables, 'Nouns' and 'Adjectives'. For every noun, there are multiple adjectives. Ex: { Noun = Apple, Adjective = [Fruit, Red, Sweet] }, { Noun = Ball, Adjective = [Red, Hard]}. There are about 5 adjectives per noun. The Adjectives table has a foreign key reference to the Id field on the Nouns table. The query I want to support is to fetch all nouns that satisfy a certain property (ex: get me all the nouns that were created at a particular time) and also satisfy an adjective. An example query would be: Get me all the nouns that were created at 7.30 AM today and have red colour in them.

My LINQ query looks as follows:

var currentTime = DateTime.Now;
var type = "Red";
return (from n in this.dbContext.Nouns where
                n.CreatedAt == currentTime && n.Adjectives.Any(a => a.AdjectiveType == type)
                orderby n.PriorityScore descending
            select new NounAdjectives
            {
                Term = n.Term,
                Adjectives = n.Adjectives
            }).Take(10).ToList();

This query ends up timing out every single time. How can I improve this?

Upvotes: 0

Views: 276

Answers (1)

dko
dko

Reputation: 718

I'm not sure if you are trying to get every adjective-noun combination where the noun meets that criteria but to get all nouns that meet that 

from n in this.dbContext.Nouns join adj this.dbContext.Adjectives on (insert FK and PK) where n.Created == currentTime AND adj.Name == "Red" Select n

Upvotes: 1

Related Questions