LINQ Join needed?

Question

Hello all.

Me again with a dumb question/scenario I need advice on.

I have the following that pulls back the contents of a column:

return getappropriateuserfield.tblAutoComplete
                    .Where(p => p.MemberId == memberid && p.ACItem == acitem)
                    .Select(p => p.ACColumn)
                    .Distinct()
                    .ToArray();

Depending upon this result, I'd like to then take the ACColumn result, go to tblPreferences, look down ColumnName, and if it matches an entry in there, pull back the Alias (present in tblPreferences)

So, for example we have tblAutoComplete:

    MemberID ACItem ACColumn
        1      2     UUF1

tblPreferences looks like

    MemberID ColumnName Alias
       1       UUF1    Category

If the user sticks in "2" as the ACItem, the first part result would be "UUF1" - the linq above does this.

How do I alter the linq so that the second part takes place, ie. takes the UUF1, looks in tblPreferences, checks out ColumnName, sees the result matches so the final result is the Alias, "Category"

Do I need to do this in 2 parts or can I do it as one query, potentially using a join?

Apologies for the thickness.

Answer 1

Looks like a join to me, which is probably most easily expressed with a query expression:

var query = from ac in foo.tblAutoComplete
            where ac.MemberId == memberid && ac.ACItem == acitem
            join pref in foo.tblPreferences.Where(x => x.MemberId == memberid)
              on ac.ACColumn equals pref.ColumnName
            select pref.Category;

Note that I've removed the Distinct() call here, which means you may get repeats. You can put Distinct() on the output, of course.

The result of will be an IQueryable<string> (assuming Category is a string). If you need more bits, you could use an anonymous type.

EDIT: I've edited the query so it's got an extra "where" clause when fetching the preferences to start with. That should be equivalent to adding MemberId to the join.