Ronald Ronald - 3 months ago 11
C# Question

Select multiple columns using Entity Framework

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

Answer

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.