使用linq下拉两列绑定

Question

使用如下SQL将两列绑定到下拉列表中

string query = "Select Id,Name+':'+Distribution_name+' 'as Name1 from BR_supervisor where( (id not in (select SupId from tbluser where active='true')) and active='true' ) ";
    DropDownList3.DataTextField = "Name1";
    DropDownList3.DataValueField = "Id";
    DropDownList3.DataBind();

现在我想将我的查询转换为linq表达式并绑定下拉列表。如何做到这一点?

Answer 1

var query = from s in db.BR_supervisor
            join u in db.tbluser.Where(x => x.active)
                 on s.id equals u.SupId into g
            where s.active && !g.Any()
            select new { 
                 s.Id, 
                 Name1 = s.Name + ":" + s.Distribution_name
            };
DropDownList3.DataTextField = "Name1";
DropDownList3.DataValueField = "Id";
DropDownList3.DataSource = query;
DropDownList3.DataBind();

生成如下SQL:

SELECT [t0].[Id], ([t0].[Name] + @p0) + [t0].[Distribution_name] AS [Name1]
FROM [BR_supervisor] AS [t0]
WHERE ([t0].[active] = 1) AND (NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [tbluser] AS [t1]
    WHERE ([t0].[id] = [t1].[SupId]) AND ([t1].[active] = 1)
    )))

Answer 2

var query2 = (from a in this._projectDataContx.BR_Supervisors
                      where ((!(from x in this._projectDataContx.tblUsers
                              where (x.Active == true)
                              select x.SupId).Contains(a.Id))&&(a.Active==true))
                      select new { Name1 = a.Name + ":" + a.Distribution_Name, a.Id });
        DropDownList3.DataSource = query2;
        DropDownList3.DataTextField = "Name1";
        DropDownList3.DataValueField = "Id";
        DropDownList3.DataBind();