Dynamic LINQ - Entity Framework 6 - Update Records for Dynamic Select

Question

C# rookie. Below is my code, been trying for hours now to get this to update some fields in my DB and tried many different implementations without luck.

        // Select all fields to update
        using (var db = new Entities())
        {
            // dbFields are trusted values
            var query = db.tblRecords
                         .Where("id == " + f.id)
                         .Select("new(" + string.Join(",", dbFields.Keys) + ")");

            foreach (var item in query)
            {
                foreach (PropertyInfo property in query.ElementType.GetProperties())
                {
                    if (dbFields.ContainsKey(property.Name))
                    {
                        // Set the value to view in debugger - should be dynamic cast eventually
                        var value = Convert.ToInt16(dbFields[property.Name]);
                        property.SetValue(item, value);

                        // Something like this throws error 'Object does not match target type'
                        // property.SetValue(query, item);
                    }
                }
            }
            db.SaveChanges();
        }

The above code when run does not result in any changes to the DB. Obviously this code needs a bit of cleanup but i'm trying to get the basic functionality working. I believe what I might need to do is to somehow reapply 'item' back into 'query' but I've had no luck getting that to work no matter what implementation I try i'm always receiving 'Object does not match target type'.

This semi similar issue reaffirms that but isn't very clear to me since i'm using a Dynamic LINQ query and cannot just reference the property names directly. https://stackoverflow.com/a/25898203/3333134

Why do you need this `Select("new ...`? If you remove the `Select` you will have entities from `tblRecords`. Changing those will cause changes that are tracked by EF. — Gert Arnold, Nov 01 '16 at 21:30

Jonathan M · Accepted Answer · 2016-11-02T13:01:17.023

Entity Framework will perform updates for you on entities, not on custom results. Your tblRecords holds many entities, and this is what you want to manipulate if you want Entity Framework to help. Remove your projection (the call to Select) and the query will return the objects directly (with too many columns, yes, but we'll cover that later).

The dynamic update is performed the same way any other dynamic assignment in C# would be, since you got a normal object to work with. Entity Framework will track the changes you make and, upon calling SaveChanges, will generate and execute the corresponding SQL queries.

However, if you want to optimize and stop selecting and creating all the values in memory in the first place, even those that aren't needed, you could also perform the update from memory. If you create an object of the right type by yourself and assign the right ID, you can then use the Attach() method to add it to the current context. From that point on, any changes will be recorded by Entity Framework, and when you call SaveChanges, everything should be sent to the database :

// Select all fields to update
using (var db = new Entities())
{
    // Assuming the entity contained in tblRecords is named "ObjRecord"
    // Also assuming that the entity has a key named "id"
    var objToUpdate = new ObjRecord { id = f.id };

    // Any changes made to the object so far won't be considered by EF

    // Attach the object to the context
    db.tblRecords.Attach(objToUpdate);

    // EF now tracks the object, any new changes will be applied

    foreach (PropertyInfo property in typeof(ObjRecord).GetProperties())
    {
        if (dbFields.ContainsKey(property.Name))
        {
             // Set the value to view in debugger - should be dynamic cast eventually
             var value = Convert.ToInt16(dbFields[property.Name]);
             property.SetValue(objToUpdate, value);
        }
    }

    // Will only perform an UPDATE query, no SELECT at all
    db.SaveChanges();
}

Worked and makes 1000x more sense thanks for the help a ton! Instead of db.Attach(objToUpdate) it believe it should be db.ObjRecord.Attach(objToUpdate). Thanks again! — user3333134, Nov 01 '16 at 23:00
@user3333134 Absolutely, my bad. That's what you get for typing it up from memory! — Jonathan M, Nov 02 '16 at 13:02

score 0 · Answer 2 · answered Nov 01 '16 at 21:37

0

When you do a SELECT NEW ... it selects only specific fields and won't track updates for you. I think if you change your query to be this it will work:

var query = db.tblRecords.Where(x=>x.id == id);

answered Nov 01 '16 at 21:37

jhilden

12,207
5
53
76

Dynamic LINQ - Entity Framework 6 - Update Records for Dynamic Select

2 Answers2