How can i write Linq2Entities Query with inner joins

Question

How can I get data from these related entities. I want to get these columns only: Term.Name , related Concept_Term.Weight, related Concept.Id

I wrote the SQL but I don't want to use

   select t.Name,ct.ConceptId,ct.Weight from Term t 
   inner join Concept_Term ct on t.Id=ct.TermId
   inner join Concept c on c.Id=ct.ConceptId
   where c.Id == 80298 and t.LanguageId=2

What I want to see is the same result like a table in a console application with the same result that I wrote in SQL.

Picture of the entities : http://img7.imageshack.us/img7/7129/77365088.jpg

Note: Sorry I can't embed this photo in my post because the system doesn't allow me to do that.

Frank Krueger · Accepted Answer · 2009-11-19 02:40:31Z

3

If you've set up you relations properly, this is quite simple:

from t in db.Terms
where t.LanguageId == 2         // Do this early on for perf
from ct in t.ConceptTerms       // This is the reverse FK: ct.TermId -> t.Id
where ct.Concept.Id == 80298    // This is the other FK: ct.ConceptId -> c.Id
select new {
    t.Name, ct.ConceptId, ct.Weight
};

This code assumes you have set your foreign keys to work in both directions.

It also demonstrates that you have some redundancy. Instead of JOINing:

where ct.Concept.Id == 80298

We could do the check directly:

where ct.ConceptId == 80298

answered Nov 19, 2009 at 2:40

Frank Krueger

71.2k48 gold badges165 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Stuntz Over a year ago

+1. This is the right answer. It is almost never right to use join in LINQ to Entities.

Yucel · Accepted Answer · 2009-11-20 17:28:56Z

I found a solution for SQL In clause, which i take and implement from the here It works well...

int[] conceptIdList = (from ct in db.Concept_Term
                               where ct.TermId == termId
                               select ct.ConceptId
                      ).ToArray();

         var result = db.Concept_Term
                      .Where(LinqExt.BuildOrExpression<Concept_Term, int>(ct => ct.ConceptId, conceptIdList))
                      .Select(ct => ct.Term.Name };

Code is for implementing SQL IN (x,y,z) clause :

    public static Expression<Func<TElement, bool>> BuildOrExpression<TElement, TValue>(
        Expression<Func<TElement, TValue>> valueSelector, 
        IEnumerable<TValue> values
    )
{     
    if (null == valueSelector) 
        throw new ArgumentNullException("valueSelector");

    if (null == values)
        throw new ArgumentNullException("values");  

    ParameterExpression p = valueSelector.Parameters.Single();

    if (!values.Any())   
        return e => false;

    var equals = values.Select(value =>
        (Expression)Expression.Equal(
             valueSelector.Body,
             Expression.Constant(
                 value,
                 typeof(TValue)
             )
        )
    );
   var body = equals.Aggregate<Expression>(
            (accumulate, equal) => Expression.Or(accumulate, equal)
    ); 

   return Expression.Lambda<Func<TElement, bool>>(body, p);
}

Collectives™ on Stack Overflow

How can i write Linq2Entities Query with inner joins

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related