Projection in Linq to SQL and Linq to Entities

Question

I am trying to get the columns dynamically. In NHibernate i can do this:

var list = _session.CreateCriteria(typeof(Person))
                   .SetProjection(Projections.ProjectionList()
                   .Add(Projections.Property("FirstName"))
                   .Add(Projections.Property("LastName"))
                   .Add(Projections.Property("Jersey"))
                   .Add(Projections.Property("FortyYard"))
                   .Add(Projections.Property("BenchReps"))
                   .Add(Projections.Property("VertJump"))
                   .Add(Projections.Property("ProShuttle"))
                   .Add(Projections.Property("LongJump"))
                   .Add(Projections.Property("PersonSchoolCollection"))
                    )
                   .SetResultTransformer(new NHibernate.Transform.AliasToBeanResultTransformer(typeof(Person)))
                   .List<Person>();

What is the equivalent in Linq?

Answer 1

As you also tag linq-to-sql and linq-to-entities I assume you are looking for an equivalent in Linq-to-Sql or Entity Framework. The two answers (so far) would be such equivalents if _session.Query<Person>() were replaced by context.Persons. (Although Darius's answer would throw an exception saying that you can't create entity instances in an entity query).

But apart from the possibility to use Select to create an ad hoc projection, one of the newer features of AutoMapper makes it even easier. Automapper is a very popular tool to map (say: project) a list of types to another list of types. But until recently its drawback was that it only worked on in-memory lists, i.e. the projection did not propagate into the SQL query. So it could not be used to cut back the number of queried fields (as NHibernate projections does).

This problem was described in Stop using AutoMapper in your Data Access Code (the title says it all). But it also offered a preliminary but excellent fix, which was later adopted by Automapper itself.

This feature makes it possible to write very succinct code like:

var dtoList = context.Persons.Project().To<PersonDto>();

(after the mapping between Person and PersonDto was registered in Automapper).

Now the SQL query only contains the fields that are used for PersonDto.

Answer 2

var list = from person in context.Persons
           select new Person()
           {
               FirstName = person.FirstName,
               LastName = person.LastName,
               Jersey = person.Jersey,
               FortyYard = person.FortyYard,
               BenchReps = person.BenchReps, 
               VertJump = person.VertJump,
               ProShuttle = person.ProShuttle,
               LongJump = person.LongJump,
               PersonSchoolCollection = person.PersonSchoolCollection
           };

Answer 3

Isn't that would work:

 _session.Query<Person>()
         .Select(new {FirstName, LastName, Jersey, FortyYard})
         .ToList()
         .Select(x => new Person() { 
                       FirstName = x.FirstName,
                       LastName = x.LastName, 
                       Jersey = x.Jersey, 
                       FortyYard = x.FortyYard
                 }
          );