I am trying to select multiple columns by using LinqToSql, but I am having a hard time with it. I am quite new in LinqToSql. When I am selecting 1 column I do it like this:
string[] name = mycontext.Users.SingleorDefault(p => p.UserId == 1).UserName;
Is there anything similar for multiple columns?
I actually want to assign Name and Surname in a ListBox control
Use Anonymous Types:
var result = mycontext.Users.Where(p => p.UserId == 1)
.Select(c => new {c.UserName , c.Family ,...})
.SingleOrDefault();
string[].
You current request will query the whole user (select *).
In most cases, it is enough and you can access to each column separately :
var user = mycontext.Users.SingleorDefault(p => p.UserId == 1);
var userName = user.Username;
var password = user.Password;
// etc...
If you only need one (or several columns) but not the whole user, you can do it like this:
var userInfo = mycontext.Users.Where(p => p.UserId == 1).Select(p => new {p.UserName, p.Password}).SingleOrDefault();
var userName = userInfo.UserName;
var password = userInfo.Password;
If you prefer a collection of string in result, you can do it this way:
List<string> userInfo = mycontext.Users.Where(p => p.UserId == 1).Select(p => new List<string> { p.UserName, p.Password }).SingleOrDefault();
var username = userInfo[0];
var password = userInfo[1];
This way, the generated query will looks like Select UserName, Password From UserTable. Its usefull only if your table has a lots of columns or a heavy one (blob for example).
How it works: by calling the Where extension methods firstly, you tell .NET to not run the query immediately. It will be executed on the SingleOrDefault call. But at this moment, the query has been completed and the whole part is done in SQL.
, in the select statement like that you've shown in your answer (.Select(p => p.UserName, p.Password)). This is an invalid LINQ statement.
Select method ! In my tests, It does not work with string[]Can't you return User object?
var user = mycontext.Users.SingleorDefault(p => p.UserId == 1)
After that you can get all properties of User without creating dynamic types
var user = mycontext.Users.SingleorDefault(p => p.UserId == 1)will select the entire User entity for you.