CODEWITHSUNDEEP
c#entity-framework
Rod
Rod

Reputation: 15433

How do I convert my Entity Framework SqlQuery to my entity list?

Environment: VS 2017 Entity Framework 6.0 

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new pubsEntities())
            {
                //this will throw an exception
                var employees = ctx.Database.SqlQuery<string>(@"
select * from employee;
                ").ToList();
            }
        }
    }

Receiving this error:

{"The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types."}

Upvotes: 0

Views: 277

Answers (2)

tschmit007
tschmit007

Reputation: 7800

It will depend on your model (type property name, sql columns name,...).

If you want to use SqlQuery:

public class MyDTO {
    string p1 {get; set;}
    int p2 {get; set;}
    //...
}

var employees = ctx.Database.SqlQuery<MyDTO>(@"
    select someString as p1, someInt as p2 from employee").ToList();

Otherwise you probably have an employees table in your context:

var employees = ctx.Employees.ToList();

or 

var employees = ctx.Employees.Where(x => x.Name == "Doe").ToList();

If you persist with string:

var employees = ctx.Database.SqlQuery<string>(
    @"select someString from employee;").ToList();

Upvotes: 1

Neil
Neil

Reputation: 11889

select * from employee

Presuming that the employee table has multiple columns (id, name, dob etc), then your query will return multiple columns. 

ctx.Database.SqlQuery<string>(...)

This will try and project those multiple columns into a string, which is not possible, hence your error message.

Upvotes: 1

Related Questions