.NET: Union in Entity Framework

Use EF to create a join query and combine data from two or more tables. The returned type can be a strongly typed object or a tuple.

copy

//strongly typed object to hold the join data
public class User_User2_Join
{
	public string DataSource { get; set; }
	public int Id { get; set; }
	public string Email { get; set; }


	public User_User2_Join() { }
	public User_User2_Join(string data_source, int id, string email)
	{
		this.DataSource = data_source;
		this.Id = id;
		this.Email = email;
	}
}


//return list of strongly typed objects
public async Task> List__Join()
{
	//table 1
	var query_users_1 = from users1 in this._DbContext.Users

				select new
				{
					DataSource = "Users1",
					Id = users1.Id,
					Email = users1.Email
				};

	//table 2
	var query_users_2 = from users2 in this._DbContext.Users2

						select new
						{
							DataSource = "Users2",
							Id = users2.Id,
							Email = users2.Email
						};


	//initialize join object with custom constructor
	//var join_list = await query_users_1.Union(query_users_2)
	//            .Select(
	//                row => new Entities.User_User2_Join(row.DataSource, row.Id, row.Email)                            
	//            )
	//            .ToListAsync();


	//initialize join object with object initializers
	var join_list = await query_users_1.Union(query_users_2)
				.Select(
					row => new Entities.User_User2_Join()
					{
						DataSource = row.DataSource,
						Id = row.Id,
						Email = row.Email
					}
				)
				.ToListAsync();

	return join_list;
}


//return list of Tuples
public async Task>> List__Join__Tuples()
{
	//table 1
	var query_users_1 = from users1 in this._DbContext.Users

						select new
						{
							DataSource = "Users1",
							Id = users1.Id,
							Email = users1.Email
						};

	//table 2
	var query_users_2 = from users2 in this._DbContext.Users2

						select new
						{
							DataSource = "Users2",
							Id = users2.Id,
							Email = users2.Email
						};


	var join_list = await query_users_1.Union(query_users_2)
					.Select(row => new Tuple(
					row.DataSource,
					row.Id,                            
					row.Email
					))
				.ToListAsync();

	return join_list;
}