sql to linq using distinct

I have the following linq statement and cannot get it to work.

the code is:

(from ll in table1
 join l in table2
 on ll.LandlordId equals l.Id
 select((value, index) => new SelectListItem 
 {
      Value = value.Id.Tostring(),
      Text = value.FullName
 })).Distinct().OrderBy(x => x.FullName).ToList()

The system is MVC and I am placing the result into a view model.

The view model is defined as IEnumerable<SelectListItem>.

The error message is:

The type of one of the expressions in the join clause is incorrect.  
Type inference failed in the call to 'Join'.
Jon Skeet
people
quotationmark

Your query expression is entirely broken at the moment - you're trying to use a mixture of query expressions and non-query-expressions. Additionally, constructing a SelectListItem and then using Distinct on that seems like a bad idea to me. You might try this:

var query = (from ll in table1
             join l in table2
             on ll.LandlordId equals l.Id
             select new { l.Id, ll.FullName })
            .Distinct()
            .OrderBy(x => x.FullName)
            .AsEnumerable() // Do the rest locally
            .Select(x => new SelectListItem {
                Value = x.Id.ToString(),
                Text = x.FullName
            })
            .ToList();

I don't know if it will work - and I've made assumptions about where you want Id and FullName to come from - but it's at least rather more valid than your current query.

people

See more on this question at Stackoverflow