0

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

Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
L. Achilles
  • 123
  • 1
  • 2
  • 14
  • 1
    `var user = mycontext.Users.SingleorDefault(p => p.UserId == 1)` will select the entire User entity for you. – Adam T Jun 20 '17 at 13:07

3 Answers3

3

Use Anonymous Types:

var result = mycontext.Users.Where(p => p.UserId == 1)
                            .Select(c => new {c.UserName , c.Family ,...})
                            .SingleOrDefault();
Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
  • string[] name = mycontext.Users.SingleorDefault(p => p.UserId == 1) .Select(c => new {c.UserName , c.Family ,...}); doesnt seem to work at all, not just for array and tha's also true, multiple columns cannot be assigned to an array like @Rahul said. – L. Achilles Jun 20 '17 at 13:16
  • @L.Achilles No. Use var instead of `string[]`. – Salah Akbari Jun 20 '17 at 13:16
  • @S.Akrabi unfortunately, it still doesn't accept SingleofDefault(p...).Select – L. Achilles Jun 20 '17 at 13:18
  • @L.Achilles. if you want to select multiple columns then you can't store them in array. simple ... that's it. – Rahul Jun 20 '17 at 13:18
  • @Rahul I never said that I want to store them in array. That's your assumption. In my question I showed how I retrieve a single column and I ask how to select multiple columns. – L. Achilles Jun 20 '17 at 13:22
  • @L.Achilles Copy and Paste my code. I didn't say `SingleofDefault(p...).Select` I said `.Select(c => new {c.UserName , c.Family}).SingleOrDefault()` – Salah Akbari Jun 20 '17 at 13:28
1

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.

fharreau
  • 2,105
  • 1
  • 23
  • 46
  • You can't use `,` in the select statement like that you've shown in your answer (`.Select(p => p.UserName, p.Password)`). This is an invalid LINQ statement. – Salah Akbari Jun 20 '17 at 13:36
  • @fharreau you are right actually, I don't have to select multiple columns from a single table, I can just select the whole table, but as UserObject I cannot create a ListBox with two columns(Name, Surname). By the way Select(p => p.UserName, p.Password) didn't work either. The compiler shows it as error. – L. Achilles Jun 20 '17 at 13:40
  • @S.Akbari : my bad, I forgot to add the new {} for creating an anonymous type :D. Fixed ! – fharreau Jun 20 '17 at 13:42
  • @L.Achilles take a look at the last par of my answer. You can create a list as a result within the `Select` method ! In my tests, It does not work with `string[]` – fharreau Jun 20 '17 at 13:43
  • @fharreau you are absolutely right. Thank you so much! – L. Achilles Jun 20 '17 at 13:50
0

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

Pablo notPicasso
  • 3,031
  • 3
  • 17
  • 22
  • so how do you create a listbox with User object. In my last case (single column listbox) I wrote: string[] names=string[] name = mycontext.Users.SingleorDefault(); foreach(string user in names){ ListItem newItem = new ListItem(names[i]); ListBox1.Items.Add(newItem); i++; } – L. Achilles Jun 20 '17 at 13:29