Using the "let" kewword in a LINQ Query with EF 4.3

Question

I have simple LINQ problem that I can't figure out. I have a table Users and a table Employees. One User can have 0...n employees.

I'd like to do something like this:

var result = from u in context.users
             where u.UsrId = 2
             let e = u.Employees.First()
             select new 
{
  UserId = u.UsrId,
  FirstName = e.FirstName
};

That does not work of course becasue the First() call is illegal. First() can only come at the end of a select. What also did not work is to select the employee in a previous query like so:

var employee = from e. in context.Employees.....

...and then say let e = employee instead of let e = u.Employees().First()

I am not sure if the use of let correct. I thought it is for subqueries.

The reason for the First() call is that the Employees table should not have more than one entry for each user. That is a mistake in the design and I have to deal with it now. So I just want the first one.

Answer 1

var result = from u in context.users
let e = u.Employees.FirstOrDefault(x => x.bossId == u.UsrId)
where u.UsrId = 2
select new  {   UserId = u.UsrId,   FirstName = e.FirstName }; 

Just a tipp without test.