How do I use EntityDatasource (Or LinqDatasource) with a Union clause

Question

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.

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:

The operation cannot be completed because the DbContext has been disposed.

any ideas will be greatly appreciated.

some type are derived from other type? if yes , Use `dbset().Union(dbset())` — mordechai, Aug 17 '14 at 21:42

Simua · Accepted Answer · 2014-08-20T06:13:51.917

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

How do I use EntityDatasource (Or LinqDatasource) with a Union clause

1 Answers1

Linked