What is wrong with LINQ to EF?

Question

I am using EF. This is my LINQ request.

public List<Tuple<int, string>> GetList() { return (from c in DALContext.MST select new Tuple<int, string>(c.CD, c.NAME)).ToList(); } 

When I call GetList (), it throws an exception: in LINQ to Entities, only parameterless constructors and initializers are supported

Instead, when I rewrite this query:

 List<Tuple<int, string>> lst = new List<Tuple<int, string>>(); var query= (from c in DALContext.MST select new{c.CD, c.NAME}); foreach (var item in query) { lst.Add(new Tuple<int,string>(item.CD,item.NAME)); } return lst; 

It just works great. What happened to my first request ???

Answer 1

Other answers are correct about what is happening, but I have not seen anyone mention how your code is best done: AsEnumerable()

  public List<Tuple<int, string>> GetList() { return (from c in DALContext.MST.AsEnumerable() select Tuple.Create(c.CD, c.NAME)).ToList(); } 

The AsEnumerable method acts as the boundary between the code that must be translated into SQL and executed on the database server, and the code that must be executed in memory after we receive a response from the database. Putting it right after the table name tells EF to get all the records from the MST table, and then run the following code, which creates tuples from the returned values.

I changed your new Tuple<int, string> to Tuple.Create mainly because I don’t like entering type parameters more than I should.

Answer 2

LINQ to EF handles queries somewhat differently than LINQ to SQL. In LINQ to EF, you cannot put a constructor with parameters in a LINQ expression, as you did here in the first bit of code:

 from c in DALContext.MST select new Tuple<int, string>(c.CD, c.NAME) 

The Tuple constructor accepts two parameters, and this is not allowed in LINQ to EF.

The explanation is explained here :

In part, this is a question of the desire of LINQ to Entities to be more explicit about the boundary between the parts of your request to execute on the server and which part is executed on the client.

With LINQ to SQL, for example, it is possible to write a LINQ query that not only includes data from the server and functions on the server, but also functions that can only be performed by the client and mix them together. The LINQ to SQL provider will then do its best to unravel things and execute the parts that it can on the server and other parts of the client. This is nice because it’s easy to simply write any query you want, and if at all possible it will work. On the other hand, it is not so nice if you accidentally write a query in which the only part that can perform the most basic thing on the server is that it returns all the data in one or more tables, and then all filtering occurs on the client (with very unpleasant primary consequences).

With LINQ to Entities, the boundaries are clearer. When you write a LINQ Query for LINQ for IQueryable Implementation objects, the entire query is executed on the server, and if some part of the query cannot be executed on the server, then an explicit border must be created with something like ToQueryable () or List (). After executing this query and the extracted data, then you can use LINQ to Objects to further refine the query, if you decide so. Here you clearly know where the boundaries are, and track performance problems and how. One of the related limitations is that the select statement in LINQ objects can be created with anonymous types or other types if they have a default constructor and custom parameters. This minimizes the likelihood that the select statement has side effects.

Answer 3

Or you could just write

 var query= (from c in DALContext.MST select new{c.CD, c.NAME}).ToList().Select(x=>new Tuple(x.CD, x.NAME)); 

This has the advantage that you only get two columns from the database.

Answer 4

Your class should have a parameterless constructor for Linq for EF, and you need to create it this way:

 public List<Tuple<int, string>> GetList() { return (from c in DALContext.MST select new Tuple<int, string>(){CD = c.CD, Name = c.NAME}).ToList(); } 

EDIT:

If you cannot add a constructor without parameters to TUPLE (which is the case here, since Tuple is not a class per se), then you have no choice with Linq for EF, but for this as a two-step process

 public List<Tuple<int, string>> GetList() { List<MST> mstList = (from c in DALContext.MST select c).ToList(); List<Tuple<int, string>> tupleList = new List<Tuple<int, string>>(); mstList.foreach(c => tupleList.add(new Tuple(c.CD, c.Name))); return tupleList; }