CODEWITHSUNDEEP
c#asp.netsql-serverlinqado.net
Malphai
Malphai

Reputation: 317

Read groupname for specific user in asp net linq

I have this DataSet of Visitors that has a column VisitorGroupID that can either be NULL or have a value that refers to another table thats called VisitorGroups. I want to read the GroupName (column belongs in VisitorGroups) for the specific Visitor in my page, how should my query look like?

This is what I have done for now, but this returns all groups existing which is wrong.

C#:

private DataSet _groups;
var results = _groups.Tables[0].AsEnumerable()
                               .GroupBy(x => x["GroupName"]).Select(g => g.First())
                               .Where(x => !string.IsNullOrEmpty(x["GroupName"].ToString()))
                               .CopyToDataTable();

Upvotes: 0

Views: 36

Answers (1)

Stephane Duteriez
Stephane Duteriez

Reputation: 934

An example to select the group of the user with the id==1:

            _groups = new DataSet();
            _groups.Tables.Add(new DataTable("users"));
            _groups.Tables.Add(new DataTable("groups"));    

                _groups.Tables[0].Columns.Add("id_user", typeof(int));
                _groups.Tables[0].Columns.Add("user_name");
                _groups.Tables[0].Columns.Add("id_group", typeof(int));
                _groups.Tables[1].Columns.Add("id_group", typeof(int));
                _groups.Tables[1].Columns.Add("group_name");
                _groups.Tables[0].Rows.Add(1, "Nom1", 1);
                _groups.Tables[0].Rows.Add(2, "Nom2", 2);
                _groups.Tables[0].Rows.Add(3, "Nom3", null);
                _groups.Tables[1].Rows.Add(1, "Group1");
                _groups.Tables[1].Rows.Add(2, "Group2");
                Console.WriteLine(_groups.GetXml());

                var result = _groups.Tables[0].AsEnumerable()
                    .Where(user => user.Field<int>("id_user") == 1)
                    .Join(_groups.Tables[1].AsEnumerable(), user => user.Field<int?>("id_group"), group => group.Field<int>("id_group"), (user, group) => new { id_user = user.Field<int>("id_user"), group_name = group.Field<string>("group_name") }).First();

                Console.WriteLine(result.group_name);

                var result2 = _groups.Tables[0].AsEnumerable()
                    .Where(user => user.Field<int>("id_user") == 3)
                    .Join(_groups.Tables[1].AsEnumerable(), user => user.Field<int?>("id_group"), group => group.Field<int>("id_group"), (user, group) => new { id_user = user.Field<int>("id_user"), group_name = group.Field<string>("group_name") }).FirstOrDefault();

                Console.WriteLine(result2 != null? result2.group_name:null);

                Console.ReadLine();

Upvotes: 1

Related Questions