none
How to get few fields with a raw sql query? (SqlQuery() method) RRS feed

  • Question

  • When I want to use raw sql query I use to do this:

    MyType myTypeDB = await dbContext.MyType.SqlQuery(
    	"select * from Mytable where where ID = 123")
    	.SingleOrDefaultAsync();

    But really in some cases I need only few fields, so to improve the performance I would like to get only the fields that I need. But when I try this:

    MyType myTypeDB = await dbContext.MyType.SqlQuery(
    	"select ID, Field1, Field2 from Mytable where where ID = 123")
    	.SingleOrDefaultAsync();

    I get an error that says that a member of the type FieldX does not have a corresponding column in the data reader with the same name.

    I would like to know if it is possible to use a raw sql query and get only few fields.

    Thank so much.

    Thursday, May 28, 2015 11:15 AM

Answers

All replies

  • if you use var instead of MyType then you won't get the error
    Thursday, May 28, 2015 11:45 AM
  • Please remember to close your previous threads by marking helpful posts as answer before you start a new one.

    >>I would like to know if it is possible to use a raw sql query and get only few fields.

    You must define a new type with that has properties for the selected fields only:

    public class YourNewType
    {
    //properties for ID, Field1, Field2
    }

    Then you could do this:

    YourNewType myTypeDB = await dbContext.Database.SqlQuery(
     "select ID, Field1, Field2 from Mytable where where ID = 123")
     .SingleOrDefaultAsync();

    Hope that helps.

    Please remember to mark posts that provide a solution to your issue as answer to close your threads and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Thursday, May 28, 2015 12:57 PM
  • But this makes me to create a class for a case, and another class when I would like to get another differents fields.

    Is no there a more generic solution?

    Thursday, May 28, 2015 2:09 PM
  • No, I don't think so. Not using the SqlQuery method. After all Entity Framework is an object relational mapping framework that maps stored data to pre-defined entity types in your application. If you want to select only a few random items from the database you could use the ordinary ADO.NET classes. You then have complete control over the SELECT statments used to fetch the data and how you create the entity objects and what properties you set on them. Entity Framework is not a replacement for these classes.

    You could also use the approach I suggested in the other thread to select only a few properties of an item: https://social.msdn.microsoft.com/Forums/en-US/f75c0c5f-5e83-4bf7-b091-94060da412b8/is-it-possible-to-select-only-few-fields-on-the-include-properties?forum=adodotnetentityframework#b07f2b01-0d6e-45f3-b99d-ecc1da28cfd2

    Or you could use use the ObjectQuery class:
    https://msdn.microsoft.com/en-us/library/vstudio/bb738512%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396

    Hope that helps.


    Please remember to mark helpful posts as answer to close your threads and then start a new thread if you have a new question.

    Thursday, May 28, 2015 2:23 PM