locked
EF How to update query result? RRS feed

  • Question

  • User-1104215994 posted

    Hello guys;

    I am lambda querying models and at the end, I would like to loop <g class="gr_ gr_74 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="74" data-gr-id="74">throuh</g> query result and update the models. Here is my sample code:

    I am getting anonymous type immutable for <g class="gr_ gr_188 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="188" data-gr-id="188">referenceId</g>. How can I fix it?

    var gameBankResult = await (context.GameBanks.Where(g => g.productCode == initiate.productCode)
                    .Take(initiate.quantity)
                    .Select(g => new {
                        g.quantity,
                        g.currency,
                        g.initiationResultCode,
                        g.productCode,
                        g.productDescription,
                        g.referenceId,
                        g.responseDateTime,
                        g.unitPrice,
                        g.totalPrice,
                        Coupons = g.coupons.Select(c => new {c.Pin, c.Serial, c.expiryDate}).ToList() //<-- Here is the projection for coupons
                    })).ToListAsync(); // I assume you are returning single entity, if not then use `.ToList()` instead of `.FirstOrDefault()`
    
                if (gameBankResult.Count() != 0)
                {
                    foreach (var item in gameBankResult)
                    {
                        item.referenceId = initiate.referenceId;
    
                    }
    
    
                    context.SaveChanges();
                    return Ok(gameBankResult);
                }

    Saturday, April 13, 2019 4:46 PM

All replies

  • User-474980206 posted
    In c# anonymous types are read only. You can only create them, not set their values. You will need use a defined class, or create a new anonymous instance from the old one.
    Saturday, April 13, 2019 5:40 PM
  • User-1104215994 posted

    sorry I couldn't get it, can you show me an example?

    Saturday, April 13, 2019 7:03 PM
  • User475983607 posted

    sorry I couldn't get it, can you show me an example?

    An example has been provided in your other similar thread.  Create a projection query and use a ViewModel to hold the results.

    https://forums.asp.net/post/6258461.aspx

    Saturday, April 13, 2019 7:05 PM
  • User1120430333 posted

    You are using a Linq projection using an anaymous type object that is a read only object, after the creation. You need to project-out using a custom type, a class that has get/set public properties.  You can use your existing EF model classes if what you are projecting matches the classes.

    You were given a link previously on how to do a Linq projection using a custom type.

    https://csharp-station.com/Tutorial/Linq/Lesson02

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/anonymous-types

    Saturday, April 13, 2019 7:07 PM
  • User-1104215994 posted

    mgebhard

    Create a projection query and use a ViewModel to hold the results.

    If I use viewModels, can I update the original model (GameBank) as follows?

    var gameBankResult = await (context.GameBanks.Where(g => g.productCode == initiate.productCode)
                    .Take(initiate.quantity)
                    .Select(g => new GameBankVM
                    {
                        quantity = g.quantity,
                        currency = g.currency,
                        initiationResultCode = g.initiationResultCode,
                        productCode = g.productCode,
                        productDescription = g.productDescription,
                        referenceId = g.referenceId,
                        responseDateTime = g.responseDateTime,
                        unitPrice = g.unitPrice,
                        totalPrice = g.totalPrice,
                        coupons = g.coupons.Select(c => new GameCouponBankVM
                        {
                            Pin = c.Pin,
                            Serial = c.Serial,
                            expiryDate = c.expiryDate
                        }).ToList() 
                    })).ToListAsync(); 
    
                if (gameBankResult.Count() != 0)
                {
                    foreach (var item in gameBankResult)
                    {
                        item.referenceId = initiate.referenceId;
    
                    }
    await context.SaveChangesAsync();
    return Ok(gameBankResult);
    }

    I am getting this error:

    The entity or complex type 'EPINMiddleWareAPI.Models.GameBankVM' cannot be constructed in a LINQ to Entities query.

    Monday, April 15, 2019 5:41 AM
  • User36583972 posted

    Hi cenk1536,

    The entity or complex type 'EPINMiddleWareAPI.Models.GameBankVM' cannot be constructed in a LINQ to Entities query.

    You can refer the following post and modify your code.

    The entity or complex type 'Categories' cannot be constructed in a LINQ to Entities query.

    The entity cannot be constructed in a LINQ to Entities query

    Best Regards

    Yong Lu

    Monday, April 15, 2019 8:04 AM
  • User-1104215994 posted

    There is something wrong with the UPDATE. The code below doesn't update the <g class="gr_ gr_40 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="40" data-gr-id="40">referenceId</g> to the GameBank table.

    if (gameBankResult.Count() != 0)
                {
                    foreach (var item in gameBankResult)
                    {
                        item.referenceId = initiate.referenceId;
    
                    }
    
    
                    await context.SaveChangesAsync();
                    return Ok(gameBankResult);
                }

    gameBankResult is type GameBankVM so it adds referenceId to GameBankVM. Since there is <g class="gr_ gr_304 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="304" data-gr-id="304">no table</g> for GameBankVM (and I don't want to create <g class="gr_ gr_371 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="371" data-gr-id="371">table</g> for GameBankVM), it updates nothing.

    Monday, April 15, 2019 8:46 AM
  • User1120430333 posted

    gameBankResult is type GameBankVM so it adds referenceId to GameBankVM. Since there is no table for GameBankVM (and I don't want to create table for GameBankVM), it updates nothing.

    https://docs.microsoft.com/en-us/ef/ef6/saving/change-tracking/entity-state

    Monday, April 15, 2019 9:34 AM
  • User-1104215994 posted

    There is something wrong as I told in my previous post.

    Here <g class="gr_ gr_49 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="49" data-gr-id="49">is</g> my sample query and update portion. I would like to UPDATE my GameBank model/table. I am querying GameBank model/table and if query returns I would like to update <g class="gr_ gr_176 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="176" data-gr-id="176">GameBank</g> model/table. But this update below does try to UPDATE GameBankVM NOT GameBank. Since there is NO GameBankVM table I am getting The entity type GameBankVM is not part of the model for the current context.

    I would like to UPDATE GameBank model/table. Any help <g class="gr_ gr_685 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="685" data-gr-id="685">please</g>.

    var gameBankResult = await (context.GameBanks.Where(g => g.productCode == initiate.productCode)
                    .Take(initiate.quantity)
                    .Select(g => new GameBankVM
                    {
                        quantity = g.quantity,
                        currency = g.currency,
                        initiationResultCode = g.initiationResultCode,
                        productCode = g.productCode,
                        productDescription = g.productDescription,
                        referenceId = g.referenceId,
                        responseDateTime = g.responseDateTime,
                        unitPrice = g.unitPrice,
                        totalPrice = g.totalPrice,
                        coupons = g.coupons.Select(c => new GameCouponBankVM
                        {
                            Pin = c.Pin,
                            Serial = c.Serial,
                            expiryDate = c.expiryDate
                        }).ToList() 
                    })).ToListAsync(); 
    
                if (gameBankResult.Count() != 0)
                {
                    foreach (var item in gameBankResult)
                    {
                        item.referenceId = initiate.referenceId;
                        context.Entry(item).State = System.Data.Entity.EntityState.Modified;
                    }
    
                    
                    await context.SaveChangesAsync();
                    return Ok(gameBankResult);
                }

    Monday, April 15, 2019 11:18 AM
  • User475983607 posted

    Your question and code are very confusing.  If all you want to do is update the database then do so.  Which means do not use a ViewModel or an anonymous type.  Simply query the DB and return the entity.  Update the fields and save.  This is fundamental EF and not complex so don't add complexity.

    Monday, April 15, 2019 11:30 AM
  • User-1104215994 posted

    Hi <g class="gr_ gr_11 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="11" data-gr-id="11">mgebhard</g>,

    Can you show me how to query and update a field on my code? <g class="gr_ gr_12 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="12" data-gr-id="12">Query</g> works OK but couldn't manage update. And here is your reply:

    "An example has been provided in your other similar thread. Create a projection query and use a ViewModel to hold the results."

    Thanks.

    Monday, April 15, 2019 12:12 PM
  • User475983607 posted

    Can you show me how to query and update a field on my code? Query works OK but couldn't manage update. And here is your reply:

    "An example has been provided in your other similar thread. Create a projection query and use a ViewModel to hold the results."

    I suggested a protection query because you were returning an anonymous type and your similar threads indicate you are trying to exclude fields from the result set.

    I recommend learning LINQ fundamentals as this is pretty simple and we've provided sample code in your other threads.

    This should work assuming there are no DB design issues.

    var gameBankResult = await (context.GameBanks.Where(g => g.productCode == initiate.productCode).ToList();

    If this is EF Core then include the "Coupon" property.

    var gameBankResult = await (context.GameBanks.Where(g => g.productCode == initiate.productCode).Include(g => g.coupon).ToList();

    https://docs.microsoft.com/en-us/ef/core/querying/related-data

    Monday, April 15, 2019 1:27 PM
  • User-1104215994 posted

    you are skipping the main point which is update :)

    1. Query and make <g class="gr_ gr_161 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="161" data-gr-id="161">projection</g>
    2. Update if there is a result

    I think first part works OK but I can't update the field.

    Monday, April 15, 2019 1:58 PM
  • User475983607 posted

    cenk1536

    you are skipping the main point which is update :)

    1. Query and make projection
    2. Update if there is a result

    I think first part works OK but I can't update the field.

    Actually you are missing the main point and do not understand Entity Framework fundamentals.  A ViewModel has nothing to do with the persistence layer.  The entity types are the persistence layer.  If you want to update the DB then you must update the Entities and save.  

    Please set aside enough time to learn the fundamentals.  The code shown is a basic one-to-many and should be very easy to update unless there is something you are not telling us.

    The syntax is similar to the following.

    var gameBankResult = await (context.GameBanks.Where(g => g.productCode == initiate.productCode).ToList();
    if(gameBankResult != null && gameBankResult.Count() > 0)
    {
    	gameBankResult.someFieldToUpdate = "The value"
    }
    
    context.SaveChangesAsync

    Monday, April 15, 2019 2:02 PM
  • User-1104215994 posted

    so without making <g class="gr_ gr_24 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="24" data-gr-id="24">projeciton</g> as you mentioned, can I update the model? If so, then somehow I need to make <g class="gr_ gr_143 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="143" data-gr-id="143">projection</g> and return the response.

    Monday, April 15, 2019 2:06 PM
  • User475983607 posted

    so without making projeciton as you mentioned, can I update the model?

    ViewModels are not the persistence layer!!!  The Entities are!

    If so, then somehow I need to make projection and return the response.

    Because your intention was not clear and you were trying to update an anonymous type.  Therefore we assume you wanted a ViewModel.  We assumed you understand EF fundamentals which is clearly not the case.

    Monday, April 15, 2019 2:09 PM
  • User-1104215994 posted

    <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="5" data-gr-id="5">herefore</g> we assume you wanted a ViewModel.  We assumed you understand EF fundamentals which <g class="gr_ gr_7 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="7" data-gr-id="7">is</g> clearly not the case.

    <g class="gr_ gr_29 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="29" data-gr-id="29">Assumption</g> is <g class="gr_ gr_65 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="65" data-gr-id="65">evil</g> mother of all mistakes :)

    With your <g class="gr_ gr_127 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="127" data-gr-id="127">guidance</g> I figured out how to accomplish query & update. I first query and update entity then make <g class="gr_ gr_457 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="457" data-gr-id="457">projection</g>. Here is my code portion inside of controller action. Any suggestions to make it more maintainable?

    var gameBankResult = await (context.GameBanks.Where(g => g.productCode == initiate.productCode)
                    .Take(initiate.quantity)).ToListAsync();
    
                if (gameBankResult.Count() != 0)
                {
                    foreach (var item in gameBankResult)
                    {
                        item.referenceId = initiate.referenceId;
                        context.Entry(item).State = System.Data.Entity.EntityState.Modified;
                    }
    
                    
                    await context.SaveChangesAsync();
    
                    var gameBankResultVM = await (context.GameBanks.Where(g => g.productCode == initiate.productCode)
                        .Take(initiate.quantity)
                        .Select(g => new GameBankVM
                        {
                            quantity = g.quantity,
                            currency = g.currency,
                            initiationResultCode = g.initiationResultCode,
                            productCode = g.productCode,
                            productDescription = g.productDescription,
                            referenceId = g.referenceId,
                            unitPrice = g.unitPrice,
                            totalPrice = g.totalPrice,
                            coupons = g.coupons.Select(c => new GameCouponBankVM
                            {
                                Pin = c.Pin,
                                Serial = c.Serial,
                                expiryDate = c.expiryDate
                            }).ToList()
                        })).ToListAsync();
    
                    
                    return Ok(gameBankResultVM);
                }

    Tuesday, April 16, 2019 5:55 AM