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.

Answer 1

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

Answer 2

Thanks for your answer, I want to ask one more question, How to get the Terms whose ConceptId is exist in an array or something else.

I try to do something but its not working :(

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

        var result = from t in db.Term
                     from ct in t.Concept_Term
                     where conceptIdList.Contains(ct.ConceptId)
                     select new { t.Name };

Answer 3

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