locked
Linq to Entity select on view returns a repeat of first record RRS feed

  • Question

  • I don't know whether there's a known bug here, or if I'm just lost on this issue and doing something wrong.

    If I run this pretty simple select query with Linq to Entity:

        var asrs = (from a in
     kom1DB.viewQualityASRperUserPerHours
                        where a.UserId == 23
                        select a).ToList<viewQualityASRperUserPerHour>();
    


    - then it returns a list with the view...but with the first record repeated 123 times!

    If I run the same query, but with Linq to SQL - it returns the 123 records, but they are all different as they are supposed to be.

    Is there a known bug, - or am I missing something here?

    Thanks!
    • Edited by Anders V Monday, September 14, 2009 3:04 PM spelling
    Monday, September 14, 2009 3:03 PM

Answers

  • This issue comes up when the keys defined in the SSDL for your view - viewQualityASRperUserPerHours, do not match what is "correct" for the actual database. For example, the key gets defined as Column1 but the result set includes multiple identical values for Column1, which makes the EF think it is getting identical objects.

    Changing the key definition for the view in the SSDL is one way of doing this, but you have to be aware that the update model from database functionality in the entity framework designer will regenerate your SSDL. So another option is to set up the view such that more columns are non-nullable: The EF will make any non-nullable column a key in the SSDL, which can help ensure that the EF does not see various rows as dupes.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, September 23, 2009 5:29 PM
  • Hi Anders,

     

    Using LINQ to SQL resolves the problem, right?   I am looking forward to your further testing results on Entity Framework.  If you need any further assistance, please feel free to let me know.

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Anders V Tuesday, September 22, 2009 7:26 AM
    Tuesday, September 22, 2009 6:39 AM

All replies

  • Hi Anders,

     

    Welcome to Entity Framework and LINQ to Entities forum!

     

    I have seen several cases about incorrect return value when using LINQ to Entities to query database views.   Most of these problems are caused by inconsistence between key definition in the database views and the Entity Framework entity key tracking features. 

     

    Could you please provide us with some demo project and testing data for further investigation?   You can directly ping me at v-micun AT microsoft.com, or upload the project and database files to http://skydrive.live.com.  

     

     

    Hope you have a great day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 15, 2009 7:18 AM
  • Hi Anders,

     

    I am writing to check the status of the issue on your side.  
     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, September 21, 2009 9:28 AM
  • Hi Lingzhi Sun,

    I took a short-cut, and made it with Linq to SQL. I think you're right that it could be a key issue, but I didn't test it. I'll put it in for later testing though, to see what the problem really is - but it makes sense for Entities to be more reliant on keys than the more direct Linq to SQL.

    Thanks,
    Anders
    Monday, September 21, 2009 9:48 AM
  • Hi Anders,

     

    Using LINQ to SQL resolves the problem, right?   I am looking forward to your further testing results on Entity Framework.  If you need any further assistance, please feel free to let me know.

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Anders V Tuesday, September 22, 2009 7:26 AM
    Tuesday, September 22, 2009 6:39 AM
  • Hi Lingzhi Sun,

    Yes, using LINQ to SQL solves the problem - although not a good solution, because that means that I cannot use LINQ to Entities in that project. Using LINQ to SQL and LINQ to Entities is in my experience not possible in the same project (from the same database), because it will define variables twice with the same name - really a mess, and it doesn't even compile.

    Regards,
    Anders
    Tuesday, September 22, 2009 7:26 AM
  • Hi Anders,

     

    Yes, using LINQ to SQL and LINQ to Entities in one project may bring some inconvenience.  One workaround would be declare different namespace for the entity classes of LINQ to SQL and LINQ to Entities.   In one of All-In-One Code Framework examples CSADONETDataService, I used LINQ to SQL and LINQ to Entities classes in one web service project to demonstrate both the technologies.  In my opinion, to use them in one real project is not we generally do.  J 

     

    I am glad that the problem is resolved.

     

    If you have any other LINQ related questions in the future, welcome to MSDN Forums!   The community members and I will be really glad to help you.

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 22, 2009 8:13 AM
  • This issue comes up when the keys defined in the SSDL for your view - viewQualityASRperUserPerHours, do not match what is "correct" for the actual database. For example, the key gets defined as Column1 but the result set includes multiple identical values for Column1, which makes the EF think it is getting identical objects.

    Changing the key definition for the view in the SSDL is one way of doing this, but you have to be aware that the update model from database functionality in the entity framework designer will regenerate your SSDL. So another option is to set up the view such that more columns are non-nullable: The EF will make any non-nullable column a key in the SSDL, which can help ensure that the EF does not see various rows as dupes.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, September 23, 2009 5:29 PM