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

Question

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."}

I suppose that you should get a List<string> not a single string — Steve
– Steve, Commented Mar 6, 2019 at 17:16

tschmit · Accepted Answer · 2019-03-06 18:33:20Z

1

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();

edited Mar 6, 2019 at 18:33

answered Mar 6, 2019 at 18:28

tschmit

7,8402 gold badges38 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Neil · Accepted Answer · 2019-03-06 17:19:48Z

1

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.

answered Mar 6, 2019 at 17:19

Neil

12.1k3 gold badges36 silver badges65 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related