Select multiple columns using Entity Framework

Question

Maybe an easy question, but can't find it easily so forgive me =) I try to select multiple columns. The statement I use is:

var dataset2 = from recordset in entities.processlists 
               where recordset.ProcessName == processname 
               select recordset.ServerName, recordset.ProcessID, recordset.Username;

Obviously, this doesn't even compile. What is the correct syntax? I also tried method based, and even tough this syntax seems correct, when accessing it throws an 'Unable to cast the type 'Anonymous type' to type 'AIM.PInfo'. LINQ to Entities only supports casting EDM primitive or enumeration types.' exception.

Any ideas?

var dataset = entities.processlists
             .Where(x => x.environmentID == environmentid && x.ProcessName == processname && x.RemoteIP == remoteip && x.CommandLine == commandlinepart)
             .Select(x => new { x.ServerName, x.ProcessID, x.Username })
             .Cast<PInfo>().ToList();

score 139 · Accepted Answer · answered Oct 23 '13 at 08:11

Indeed, the compiler doesn't know how to convert this anonymous type (the new { x.ServerName, x.ProcessID, x.Username } part) to a PInfo object.

var dataset = entities.processlists
    .Where(x => x.environmentID == environmentid && x.ProcessName == processname && x.RemoteIP == remoteip && x.CommandLine == commandlinepart)
    .Select(x => new { x.ServerName, x.ProcessID, x.Username }).ToList();

This gives you a list of objects (of anonymous type) you can use afterwards, but you can't return that or pass that to another method.

If your PInfo object has the right properties, it can be like this :

var dataset = entities.processlists
    .Where(x => x.environmentID == environmentid && x.ProcessName == processname && x.RemoteIP == remoteip && x.CommandLine == commandlinepart)
    .Select(x => new PInfo 
                 { 
                      ServerName = x.ServerName, 
                      ProcessID = x.ProcessID, 
                      UserName = x.Username 
                 }).ToList();

Assuming that PInfo has at least those three properties.

Both query allow you to fetch only the wanted columns, but using an existing type (like in the second query) allows you to send this data to other parts of your app.

Andre Lombaard · Answer 2 · 2013-10-23T08:12:04.430

You can select to an anonymous type, for example

var dataset2 = 
    (from recordset in entities.processlists 
    where recordset.ProcessName == processname 
    select new
    {
        serverName = recordset.ServerName,
        processId = recordset.ProcessID, 
        username = recordset.Username
    }).ToList();

Or you can create a new class that will represent your selection, for example

public class MyDataSet
{
    public string ServerName { get; set; }
    public string ProcessId { get; set; }
    public string Username { get; set; }
}

then you can for example do the following

 var dataset2 = 
    (from recordset in entities.processlists 
    where recordset.ProcessName == processname 
    select new MyDataSet
    {
        ServerName = recordset.ServerName,
        ProcessId = recordset.ProcessID, 
        Username = recordset.Username
    }).ToList();

The 2nd example worked really well, thanks a lot. I was having performance issues iterating through each object. This helped me select it once from the database and do the rest in code/memory. — Riaan, Sep 14 '15 at 18:21

score 12 · Answer 3 · answered Oct 23 '13 at 08:08

12

Why don't you create a new object right in the .Select:

.Select(x => new PInfo{ 
    ServerName = x.ServerName, 
    ProcessID = x.ProcessID, 
    UserName = x.Username }).ToList();

answered Oct 23 '13 at 08:08

tmp001

149
3

score 5 · Answer 4 · edited Dec 11 '18 at 22:11

It's correct way to get data in specified type:

var dataset = entities.processlists
         .Where(x => x.environmentID == environmentid && x.ProcessName == processname && x.RemoteIP == remoteip && x.CommandLine == commandlinepart)
         .Select(x => new { x.ServerName, x.ProcessID, x.Username })
         .ToList() /// To get data from database
         .Select(x => new PInfo()
              { 
                   ServerName = x.ServerName, 
                   ProcessID = x.ProcessID, 
                   Username = x.Username 
              });

For more information see: The entity cannot be constructed in a LINQ to Entities query

score 3 · Answer 5 · answered Oct 23 '13 at 08:06

You either want to select an anonymous type:

var dataset2 = from recordset 
               in entities.processlists 
               where recordset.ProcessName == processname 
               select new 
               {
                recordset.ServerName, 
                recordset.ProcessID, 
                recordset.Username
               };

But you cannot cast that to another type, so I guess you want something like this:

var dataset2 = from recordset 
               in entities.processlists 
               where recordset.ProcessName == processname 

               // Select new concrete type
               select new PInfo
               {
                ServerName = recordset.ServerName, 
                ProcessID = recordset.ProcessID, 
                Username = recordset.Username
               };

score 1 · Answer 6 · answered Nov 10 '15 at 18:02

var test_obj = from d in repository.DbPricing
join d1 in repository.DbOfficeProducts on d.OfficeProductId equals d1.Id
join d2 in repository.DbOfficeProductDetails on d1.ProductDetailsId equals d2.Id
    select new
    {
    PricingId = d.Id,
    LetterColor = d2.LetterColor,
    LetterPaperWeight = d2.LetterPaperWeight
    };


http://www.cybertechquestions.com/select-across-multiple-tables-in-entity-framework-resulting-in-a-generic-iqueryable_222801.html

score 0 · Answer 7 · edited Apr 15 '22 at 17:55

0

var dataset = entities.processlist
    .SqlQuery("select ServerName,ProcessID,Username where environmentID = environmentid and CommandLine = commandlinepart")
    .ToList();

edited Apr 15 '22 at 17:55

qxotk

2,384
5
24
39

answered Apr 14 '22 at 14:19

Saeid Shariati

11
1

Your answer could be improved by adding more information on what the code does and how it helps the OP. – Tyler2P Apr 17 '22 at 17:10
This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/31537721) – Julia Apr 17 '22 at 23:02

score -1 · Answer 8 · edited Nov 22 '16 at 10:32

Here is a code sample:

var dataset = entities.processlists
    .Where(x => x.environmentID == environmentid && x.ProcessName == processname && x.RemoteIP == remoteip && x.CommandLine == commandlinepart)
    .Select(x => new PInfo 
                 { 
                      ServerName = x.ServerName, 
                      ProcessID = x.ProcessID, 
                      UserName = x.Username 
                 }) AsEnumerable().
               Select(y => new PInfo
               {
                   ServerName = y.ServerName,
                   ProcessID = y.ProcessID,
                   UserName = y.UserName 
               }).ToList();

Select multiple columns using Entity Framework

8 Answers8

Linked