none
How to get the data from a generic query issued from the Entity Framework into a grid. RRS feed

  • Question

  • I am having problems with the Entity Framework but I would still like to eventually use it.  One of the problems is we have a filed in a table with the same name as the table.  I don't know if there is a viable solution for that.

    What I would like to do to keep the project on schedule is keep the framework dll part of the project but issue queries where ever I have problems.  Then I can go back and correct things at my leisure.  But I don't know how to get to the data returned.

    If someone could give me a sample it would go a long way.  For example if I have this code:

                    var data = DM.BillingOrderEntries.SqlQuery("SELECT DateOfBirth, FirstName, LastName, A.Accession, Requisition " +
                                                                    "FROM Accessions A " +
                                                                    "JOIN BillingOrderEntry BOE on A.Accession = BOE.Accession " +
                                                                    "ORDER BY LastName");
    

    How do I get the results into a grid.  It seems to run OK but I cant look at it.

    Thanks

    Friday, September 2, 2016 7:05 PM

Answers

All replies

  • I am having problems with the Entity Framework but I would still like to eventually use it.  One of the problems is we have a filed in a table with the same name as the table.  I don't know if there is a viable solution for that.

    What I would like to do to keep the project on schedule is keep the framework dll part of the project but issue queries where ever I have problems.  Then I can go back and correct things at my leisure.  But I don't know how to get to the data returned.

    If someone could give me a sample it would go a long way.  For example if I have this code:

                    var data = DM.BillingOrderEntries.SqlQuery("SELECT DateOfBirth, FirstName, LastName, A.Accession, Requisition " +
                                                                    "FROM Accessions A " +
                                                                    "JOIN BillingOrderEntry BOE on A.Accession = BOE.Accession " +
                                                                    "ORDER BY LastName");

    How do I get the results into a grid.  It seems to run OK but I cant look at it.

    Thanks

    Well, set a breakpoint, and use Quickwatch and see what is in data. Is it a collection of objects with properties on the object based on the columns in the Select statement or what?


    Friday, September 2, 2016 8:01 PM
  • Hi MarDude,

    As DA924x said, you could set a breakpoint and check the structure of data, before you set the breakpoint, I would suggest that you could add ToList() method on your code snippet (code as below). Because The SqlQuery method on DbSet allows a raw SQL query to be written that will return entity instances. The returned objects will be tracked by the context just as they would be if they were returned by a LINQ query. Note that, just as for LINQ queries, the query is not executed until the results are enumerated

    var data = DM.BillingOrderEntries.SqlQuery("SELECT DateOfBirth, FirstName, LastName, A.Accession, Requisition " +
                                                                    "FROM Accessions A " +
                                                                    "JOIN BillingOrderEntry BOE on A.Accession = BOE.Accession " +
                                                                    "ORDER BY LastName").ToList();

    For more information, please refer to:

    https://msdn.microsoft.com/en-sg/data/jj592907.aspx

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 5, 2016 1:33 AM
    Moderator
  • Thanks for your reply Cole.  I am unfamiliar with the objects that EF returns so that is part of the issue.  For data I get a type of System.Data.Entity.Infrastructure.DbSqlQuery`1[VermillionData.BillingOrderEntry]

    Dont know how to use that type so I did not know if data is comming back.

    When I add the ToList() it fails because of columns in the table that I do not need.  If I change my select to SELECT * then it works.  But I don't want dozens of columns when a half dozen or so is all I want.  Is this a limitation with EF?  Perhaps I made a mistake in choosing this tool.

    Thanks

    Tuesday, September 6, 2016 1:25 PM
  • First, you don't need a SQL Query for this.  And you can project the query results into an arbitrary type, either an anonymous type, or a named type.

    eg

                    var q = from o in db.BillingOrderEntries
                            where o.Accession != null
                            orderby o.LastName
                            select new { o.DateOfBirth, o.FirstName, o.LastName, o.Accesion.Assesion, o.Requisition };
    
                    var data = q.ToList();
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, September 6, 2016 1:36 PM
  • Thanks for your reply David, but your solution does not address the question.  If you have an answer instead of a work-around I would be pleased to hear it.  Maybe if explained . . . 

    Actually I do need SQL since EF is causing numerous problems that I can not find the answer for and if it delays any further, I will have to go with a custom data manager.  I would like to get this project out using EF so I can buy some time to figure out out to correct all the issues.

    Problem #1 is I have a table with a field that has the same name.  I posted that question on stack overflow but no one there could give me the instructions on how to fix it http://stackoverflow.com/questions/39297949/how-to-get-a-subset-of-fields-from-entity-framework-from-a-join-with-a-field-tha  Thats what drove me to use a direct query.

    Problem # 2 occured when I tried moving the code to its own library, which would be a requirement for future development.   It failed.  I asked here but again did not get an acceptable solution. https://social.msdn.microsoft.com/Forums/en-US/d811fb4d-a04d-4509-9638-0f2d192c01c1/how-to-use-the-entity-framework-in-a-different-assembly?forum=adodotnetentityframework

    So my hope was to at least get this project out using EF (with a direct query bundled with the UI) so I went for straight SQL but that did not work either.  So I spent the day writing my own data manager and got more done in a half a day than I did with EF in 3 days.  I still am hopeful it would save time in the long run, but we cant wait for weeks to get something out until I figure out all the quirks in the framework.  I was hoping to get some guidance but I did not.  Plus the more issues that surface cause the gain from using EF to diminish.  

    Someone recommended formal training but that alone would completely destroy any benefit.  The whole point of using EF would be to get something done easier and quicker (read: cheaper) which makes investors happy.  Adding cost (training) or delays (give me a week to read this book) and the savings start evaporating.  Not to mention things that are sure to surface later that can take twice or more of the time to figure out as opposed to something known (straight SQL).

    Any suggestions, I'm open to them.  But as it stands right now using EF is not looking good.

    Tuesday, September 6, 2016 7:44 PM
  • >as it stands right now using EF is not looking good.

    Some people prefer Dapper as a lightweight alternative to using EF.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, September 6, 2016 8:48 PM