How to Select one element from a linq multiple join table?-CodePudding

var queryInfo = (from p in table1
                 join q in table2 on p.TABLEID equals q.USERNAME
                 join b in table3 on p.ORIGINAL_USER equals b.USERNAME
                 where p.NAME == IdVal
                 select new 
                        { 
                            p.NAME,
                            p.ID,
                            p.EXCHANGE,
                            p.CREATION,
                            q.USERNAME,
                            q_email = q.EMAIL,
                            q_fullname = q.FULL_NAME,
                            b_email = b.EMAIL,
                            p.ORIGINAL_USER,
                            b_fullname = b.FULL_NAME
                        });

Name = queryInfo.ToList().ElementAt(0).ToString();
ID = queryInfo.ToList().ElementAt(1).ToString();
exchange = queryInfo.ToList().ElementAt(2).ToString();
Creation = queryInfo.ToList().ElementAt(3).ToString();
AUsername = queryInfo.ToList().ElementAt(4).ToString();
AEmail = queryInfo.ToList().ElementAt(5).ToString();
AFullName = queryInfo.ToList().ElementAt(6).ToString();
EEmail = queryInfo.ToList().ElementAt(7).ToString();
EUsername = queryInfo.ToList().ElementAt(8).ToString();
EFullName = queryInfo.ToList().ElementAt(9).ToString();

The query is correct and working, I'm having problem trying to select and assign one to each declared variable.

I tried

queryInfo.ToList().ElementAt(0).ToString();

but this is not working. What is the proper syntax?

CodePudding user response：

Create a custom class so you can map your resut into

class:

public class User
{
    public string Name { get; set; }
    public string ID { get; set; }
    public string exchange { get; set; }
    public string Creation { get; set; }
    public string AUsername { get; set; }
    public string AEmail { get; set; }
    public string AFullName { get; set; }
    public string EEmail { get; set; }
    public string EUsername { get; set; }
    public string EFullName { get; set; }
}

mapping:

User result = (from p in table1
                    join q in table2 on p.TABLEID equals q.USERNAME
                    join b in table3 on p.ORIGINAL_USER equals b.USERNAME
                    where p.NAME == IdVal
                    select new User()
                    {
                        Name = p.NAME,
                        ID = p.ID,
                        exchange = p.EXCHANGE,
                        Creation = p.CREATION,
                        AUsername = q.USERNAME,
                        AEmail = q.EMAIL,
                        AFullName = q.FULL_NAME,
                        EEmail = b.EMAIL,
                        EUsername = p.ORIGINAL_USER,
                        EFullName = b.FULL_NAME
                    }).FirtstOrDefault();

CodePudding user response：

Materialize query via FirstOrDefault() and retrieve property values:

var queryInfo = 
    (from p in table1
    join q in table2 on p.TABLEID equals q.USERNAME
    join b in table3 on p.ORIGINAL_USER equals b.USERNAME
    where p.NAME == IdVal
    select new 
    { 
        p.NAME,
        p.ID,
        p.EXCHANGE,
        p.CREATION,
        q.USERNAME,
        q_email = q.EMAIL,
        q_fullname = q.FULL_NAME,
        b_email = b.EMAIL,
        p.ORIGINAL_USER,
        b_fullname = b.FULL_NAME
    })
    .FirstOrDefault();

Name = queryInfo?.NAME;
ID = queryInfo?.ID.ToString();
exchange = queryInfo?.EXCHANGE;
Creation = queryInfo?.CREATION.ToString();
AUsername = queryInfo?.USERNAME;
AEmail = queryInfo?.q_email;
AFullName = queryInfo?.q_fullname;
EEmail = queryInfo?.b_email;
EUsername = queryInfo?.ORIGINAL_USER;
EFullName = queryInfo?.b_fullname;