LINQ join failing between table and list of objects

Question

I need to perform an update on a table with values from a List of objects in C# .NET Core 3.0. I tried to use the Join method, but receive this error:

Processing of the LINQ expression

DbSet<Room>
    .Join(
        outer: __p_0, 
        inner: p => p.RoomId, 
        outerKeySelector: s => s.ruId, 
        innerKeySelector: (s, p) => new { 
            kuku = s, 
            riku = p
         })

by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See link for more detailed information.

    public class Room
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Key]
        public int RoomId { get; set; }

        [StringLength(50, MinimumLength = 3)]
        public string RoomAddress { get; set; }
    }
    public class roomsForUpdate 
    {
        public int ruId { get; set; }
        public string ruName { get; set; }
    }

var roomList = new List<roomsForUpdate>() { new roomsForUpdate  { ruId = 1, ruName = "aa" }, new roomsForUpdate { ruId = 2, ruName = "bb" } };
var result = _context.Room.Join(roomList, p => p.RoomId, s => s.ruId, (s, p) => new { kuku = s, riku = p }).ToList();

Answer 1

LINQ is not meant to change the sources, it can only extract data from the sources. If you need to update data, you first fetch the items that must be updated, then you update them. Alternatively you can use plain old SQL to update the data without fetching it first.

In local memory, you have a sequence of RoomsForUpdate. Every RoomForUpdate has an Id (RuId) and a Name.

In your database you have a table with Rooms, Every Room in this table has an Id in RoomId and a RoomAddress.

It seems to me, that you want to update all Rooms that have an RoomId, that is one of the RuIds in your sequence of RoomsForUpdate. In other words: fetch (some properties of) all Rooms that have a value for RoomId that is a RuId in your sequence of RoomsForUpdate:

var roomsToUpdate = new List<roomsForUpdate>()
{
    new roomsForUpdate  { ruId = 1, ruName = "aa" },
    new roomsForUpdate { ruId = 2, ruName = "bb" }
};

// Extract the Ids of the rooms that must be fetched
var roomToUpdateIds = roomsToUpdate.Select(room => room.ruId);

// Fetch all rooms from the database that have a RoomId that is in this sequence
var fetchedRooms = dbContext.Rooms
    .Where(room => roomToUpdateIds.Contains(room => room.RoomId)
    .ToList();

Of course you can put everything into one big LINQ statement. This will not improve efficiency, however it will deteriorate readability of your code.

Now to update the Rooms, you'll have to enumerate them one by one, and give the fetched rooms new values. You didn't say which new value you want. I have an inkling that you want to assign RuName to RoomAddress. This means that you have to combine the Room with the new value for the RoomAddress.

This can be done by LINQ:

var roomsWithExpectedNewValues = fetchedRooms.Join(roomsToUpdate,

    fetchedRoom => fetchedRoom.RoomId,   // from every fetched room take the Id
    roomToUpdate => roomToUpdate.RuId,   // from every room to update take the RuId

    // for every fetchedRoom with its matching room to update, make one new:
    (fetchedRoom, roomToUpdate) => new
    {
        Room = fetchedRoom,
        NewValue = roomToUpdate.RuName,
    })
    .ToList();

To actually perform the update, you'll have to enumerate this sequence:

foreach (var itemToUpdate in roomsWithExpectedNewValues)
{
    // assign RuName to RoomName
    itemToUpdate.Room.RoomName = itemToUpdate.NewValue;
}
dbContext.SaveChanges();

A little less LINQ

Although this works, there seems to be a lot of magic going on. The join will internally make a Dictionary for fast lookup, and throws it away. I think a little less LINQ will make it way more easy to understand what's going on.

// your original roomsToUpdate
var roomsToUpdate = new List<roomsForUpdate>()
{
    new roomsForUpdate  { ruId = 1, ruName = "aa" },
    new roomsForUpdate { ruId = 2, ruName = "bb" }
};

var updateDictionary = roomsToUpdate.ToDictionary(
    room => room.RuId,         // key
    room => room.RuName)       // value

The Keys of the dictionary are the IDs of the rooms that you want to fetch:

// fetch the rooms that must be updated:
var fetchedRooms = dbContext.Rooms
    .Where(room => updateDictionary.Keys.Contains(room => room.RoomId)
    .ToList();

// Update:
foreach (var fetchedRoom in fetchedRooms)
{
    // from the dictionary fetch the ruName:
    var ruName = updateDicationary[fetchedRoom.RoomId];

    // assign the ruName to RoomAddress
    fetchedRoom.RoomAddress = ruName;

    // or if you want, do this in one statement:
    fetchedRoom.RoomAddress = updateDicationary[fetchedRoom.RoomId];
}
dbContext.SaveChanges();

Answer 2

You cannot join the EF Core LINQ query with a local list, because it can't be translated into SQL. Better first you get the database data and then join in memory.