LINQ to SQL MAX in WHERE clause

Question

I am new to Linq so as expected I have encountered difficulties. What I am trying to achieve is this:

SELECT id, name, password
FROM users u
WHERE u.id = (SELECT MAX(u1.id) FROM users u1);

My Linq is:

var dbUsers = from u in context.Users
              where u.Id == (context.Users.Max(u1 => u1.Id))
              select u;

But I always end with the following exception:

Unable to create a constant value of type 'Bla.Users'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Here is the users class:

public class Users
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Password { get; set; }
    }
}

Here is my context class:

 public class EFDbContext : DbContext
    {
        public DbSet<User> Users{ get; set; }
    }

Answer 1

You need to select the ID property

var dbUsers = from u in context.Users
              where u.Id == (context.Users.Select(u1 => u1.Id).Max())
              select u;

Answer 2

I usually do my LINQing in lambda format...

var dbUsers = DataContext.Users
    .Where(u => u.Id == (DataContext.Users.Max(u1 => u1.Id)))
    .Select(u => new
    {
       Id = u.Id,
       Name = u.Name,
       Password = u.Password
    });

If you want the comprehension format...

var dbUsers = from u in DataContext.Users
    where u.Id == (DataContext.Users.Max(u1 => u1.Id))
    select new 
    {
       Id = u.Id,
       Name = u.Name,
       Password = u.Password
    };

Answer 3

Consider using a let statement:

var dbUsers = from u in context.Users
              let int maxId = context.Users.Max(u1 => u1.Id)
              where u.Id == maxId
              select u;

Answer 4

Please let me know if this one solves your problem:

var dbUser = (from u in context.Users
              orderby u.Id descending).FirstOrDefault()