Can LINQ2SQL return a few columns or is it entity or single column only?

Question

Hi,

From what I understand, using something like nHibernate will return either a single result like int (say returning a count) but it can't return say 2 columns or 3 columns from the users table.

Is linq the same way?

e.g. say I have the Users table with columns: UserID, username, password, email, datecreated)

Could it return UserID, password ONLY, or it can only return the entire row/entity/class?

thanks for clearing this up.

Answer 1

Absolutely it can do what you like. Try and return those fields into an anonymous type. For more info, check out this article by Scott Guthrie on Anonymous Types. Here's an example:

var twoFields = from c in db.Customers
                where c.ID == someCustomerID
                select new { ID = c.ID, Name = c.Name}

//use your new object called twoFields
Console.WriteLine(twoFields.Name);

Answer 2

You can retrieve only some columns by returning the result as an anonymous type. That can look like this:

var selectedUserId = 19;

var userInfo = from u in context.Users
where u.UserId == selectedUserId 
select new { u.UserId, u.Password };

It can also look like this, if you don't use query syntax:

var selectedUserId = 19;

var userInfo = context.Users
                      .Where(u => u.UserId == selectedUserId)
                      .Select(u => new { u.UserId, u.Password });

That will return an IQueryable instance, you can add a call to .SingleOrDefault() or .FirstOrDefault() at the end to get back a single object of your anonymous type.

Answer 3

Yes it can be done with Linq to SQL, as bdukes and pcampbell has shown, but it can be done with NHibernate as well.

For example you could execute the following query, which would return a list of object[]:

var userData = session.CreateQuery(
    "select u.Username, u.Password from User u").List();

If you want to you could select only some of the fields, but still get back an entity, so that you don't have to mess with object arrays. This can be done with HQL and Critera as well.

For example, if the user class has a constructor that takes a username and password:

var users = session.CreateQuery(
    "select new User(u.Username, u.Password) from User u").List<User>();

Answer 4

NHibernate can do this too:

var selectedUserId = 19;

var userInfo = from u in session.Linq<Users>()
where u.UserId == selectedUserId 
select new { Id = u.UserId, Password = u.Password };

What it can't do is return a User entity with lazily loaded columns. I have no idea if Linq to SQL can lazy load individual columns.

What this is doing is a projection.