我如何使用EntityDatasource（或使用LinqDataSource）与欧盟条款 [英] How do I use EntityDatasource (Or LinqDatasource) with a Union clause

问题描述

如何合并两个表中的记录在EntityDataSource控件？我用Google搜索它，没有运气SO搜查。什么，我需要在SQL是

How do I combine records from two tables in an Entitydatasource control? I have googled it and searched on SO with no luck. The SQL of what I need is

SELECT DISTINCT username FROM 
(SELECT s.username FROM project_stakeholders s
UNION
SELECT t.username FROM project_team_members t)

我的实体结构如下：

My entities structure is as follows:

project_stakeholders
----------------------
project_stakeholders.record_id (PK)
project_stakeholders.username
project_stakeholders.project
project_stakeholders.project_id (FK)
project_stakeholders.status

project_team_members
---------------------
project_team_members.record_id (PK)
project_team_members.username
project_team_members.project
project_team_members.project_id (FK)
project_team_members.status

我发现这个问题，几乎提供了一个回答我的问题，但我的是更先进的，因为我的子查询从没有涉及到的主要实体的实体。

I found this question that almost provides an answer to my question but mine is more advanced because my SubQueries are from Entities not related to the primary entity.

我已经改变了我的数据源LiqDatasource控制在code LinqDataSource控件的Onselecting事件背后添加了code如下：

I have changed my Datasource to LiqDatasource Control and added the code below in the Code Behind of the Onselecting Event of the LinqDatasource Control:

    protected void TaskProfileDS_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {

        int project_id = 0;

        if (Int32.TryParse(Request.QueryString["id"], out project_id))
        {

            using (MyEntitiesContext db = new MyEntitiesContext())
            {
                var TeamMembers = from tm in db.team_members where tm.project_id == project_id select tm.username;
                var Stakeholders = from sh in db.project_stakeholders where sh.project_id == project_id select sh.username;
                var result = from usr in db.project_profiles where (TeamMembers.Contains(usr.caption) || Stakeholders.Contains(usr.caption)) select usr;

                e.Result = result;

            }
        }
    }

但不幸的是，我得到以下错误：

But unfortunately, I get the following error:

操作无法完成。

任何想法将大大AP preciated。

any ideas will be greatly appreciated.

推荐答案

下面是code，它为我工作来从不相关的表（实体）的记录。我用的LinqDataSource，然后在QueryCreated事件中，我改变了查询，以我自己的LINQ查询：

Here is the code that worked for me to fetch records from unrelated tables (entities). I used LinqDataSource, then in the QueryCreated event, I changed the query to my own LINQ query:

protected void TaskDS_QueryCreated(object sender, QueryCreatedEventArgs e)
    {
        int project_id = 0;

        if (Int32.TryParse(Request.QueryString["id"], out project_id))
        {

            using (SDManagerEntities db = new SDManagerEntities())
            {
                List<project_profiles> usrs = new List<project_profiles>();

                var users1 = db.team_members.Where(t => t.project_id == project_id).Select(it => it.username);
                var users2 = db.project_stakeholders.Where(s => s.project_id == project_id).Select(it => it.username);

                var allMembers = users1.Concat(users2);

                foreach (var member in allMembers.Distinct())
                {
                    project_profiles usr = db.project_profiles.SingleOrDefault(it => it.caption == member);
                    if (!usrs.Contains(usr))
                    {
                        usrs.Add(usr);
                    }
                }

                e.Query = usrs.AsQueryable().OrderBy(it => it.fullName);

            }
        }
    }

我希望它能帮助别人

I hope it helps someone else

这篇关于我如何使用EntityDatasource（或使用LinqDataSource）与欧盟条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！