Entity with List in Select statement

Beginning to think the below is not possible due to errors and lack of supporting evidence in logic but wanted to confirm.

We're using LINQ to build an entity SQL statement but need to use a List in the Select statement to gather additional data. This data is from a previous Entity collection in app memory. Doing the same thing straight up with Entity works without issue.

The pseudo-code looks like:

List<ParticipationStatus> lstStatuses = GetStatuses();

var data = (
    from user in dbContext.User
	where user.JoinDate >= *date*
	select new CustomObject 
	{
		Name = user.FullName,
		ParticipationStatus = lstStatuses
			.Where(m => m.ParticipationStatusID == user.ParticipationStatusID)
            .Select(x => x.Label)
			.FirstOrDefault() + " Some string here"
	} )
	.ToList();

Just wanted some confirmation of this possibility.

Assuming you have a ParticipationStatus navigation-property set-up, all you need is this:

var usersWithStatus = await dbContext.Users
    .Where( u => u.JoinDate >= someDateTime )
    .Select( u => new CustomObject
    {
        Name = u.FullName,
        ParticipationStatus = u.ParticipationStatus.Label + " Some string here"
    } )
    .ToListAsync();

If you really want to re-use your existing List<ParticipationStatus> lstStatuses = GetStatuses(); (which I think is a bad idea: because as far as we can see, there is no guarantee that lstStatuses actually contains every ParticipationStatus referneced by every User, and your code dopesn't appear to be using a transaction to ensure isolated or consistent reads), but you can do the Join in-memory after loading users:

List<ParticipationStatus> lstStatuses = GetStatuses();

List<User> users = await dbContext.Users
    .Where( u => u.JoinDate >= someDateTime )
    .ToListAsync();

List<CustomObject> usersWithStatus = users
    .Join(
        inner: lstStatuses,
        outerKeySelector: u => u.ParticipationStatusID,
        innerKeySelector: s => s.ParticipationStatusID,
        resultSelector: ( u, s ) => new CustomObject
        {
            Name = u.FullName,
            ParticipationStatus = s.Label + " Some string here"
        }
    )
    .ToList();

• Linq's Join is an INNER JOIN operation (and not any other kind of join, like FULL OUTER JOIN). Note that, as with SQL's INNER JOIN, it is possible for multiple rows in inner and outer to match each-other which may be undesirable.

  • And if any User entities aren't matched to a lstStatuses item then that User will be silently excluded from the results - whereas I'd consider that exceptional and if this were my program I'd want it to throw an exception instead of silently excluding it.

• Internally, the .Join will efficiently map User and ParticipationStatus in linear-time O(n[u] + n

  展开收缩
  ) by using a temporary hashtable, whereas your current code takes quadratic time: O(n[u] * n
  展开收缩
  ).