如何使用LINQ实现Union操作？多表获取指定客户邮箱方法问询

Hey there! Let's break down your question step by step.

Absolutely! UNION is designed to combine rows from two compatible result sets and automatically remove duplicates. If your Customer table stores a primary email and the Customer Email table holds secondary/extra emails for the same customer, you can use UNION to merge these sources, then aggregate the emails into the space-separated format you want.

Here's a SQL example (adjust the column names to match your actual schema):

SELECT 
  customerId,
  STRING_AGG(email, ' ') AS EmailIds -- Use GROUP_CONCAT(email, ' ') for MySQL
FROM (
  -- Pull primary email from Customer table
  SELECT customerId, PrimaryEmail AS email
  FROM Customer
  WHERE customerId = 4
  UNION
  -- Pull all emails from Customer Email table
  SELECT customerId, EmailId AS email
  FROM CustomerEmail
  WHERE customerId = 4
) CombinedEmails
GROUP BY customerId;

• Use UNION if you want to remove duplicate emails (if the same email exists in both tables).
• Use UNION ALL instead if you want to keep duplicates (equivalent to merging without deduplication).

This query will return exactly the format you requested: customerId:4 EmailId: sumit.printzone@gmail.com vinod.printzone@gmail.com.

LINQ's Union() method mirrors SQL's UNION behavior—it combines two sequences and removes duplicates. Below are examples for both database queries (EF Core/LINQ to SQL) and in-memory collections.

1. LINQ to SQL / EF Core (Database Query) #

First, assume you have these entity classes matching your tables:

public class Customer
{
    public int CustomerId { get; set; }
    public string PrimaryEmail { get; set; }
}

public class CustomerEmail
{
    public int CustomerId { get; set; }
    public string EmailId { get; set; }
}

Then the LINQ query:

using (var dbContext = new YourDatabaseContext())
{
    // Get primary email from Customer table
    var primaryEmails = dbContext.Customers
        .Where(c => c.CustomerId == 4)
        .Select(c => new { c.CustomerId, Email = c.PrimaryEmail });

    // Get all emails from CustomerEmail table
    var secondaryEmails = dbContext.CustomerEmails
        .Where(ce => ce.CustomerId == 4)
        .Select(ce => new { ce.CustomerId, Email = ce.EmailId });

    // Combine with Union (removes duplicates)
    var combinedEmails = primaryEmails.Union(secondaryEmails).ToList();

    // Aggregate into the desired string format
    var result = combinedEmails
        .GroupBy(ce => ce.CustomerId)
        .Select(g => new 
        {
            CustomerId = g.Key,
            EmailIds = string.Join(" ", g.Select(x => x.Email))
        })
        .FirstOrDefault();

    // Output the result
    if (result != null)
    {
        Console.WriteLine($"customerId:{result.CustomerId} EmailId: {result.EmailIds}");
    }
}

2. LINQ to Objects (In-Memory Collections) #

If your data is already in memory (e.g., lists), the approach is almost identical:

// Sample in-memory data
var customers = new List<Customer>
{
    new Customer { CustomerId = 4, PrimaryEmail = "sumit.printzone@gmail.com" }
};

var customerEmails = new List<CustomerEmail>
{
    new CustomerEmail { CustomerId = 4, EmailId = "vinod.printzone@gmail.com" }
};

// Combine emails
var primaryEmails = customers
    .Where(c => c.CustomerId == 4)
    .Select(c => new { c.CustomerId, Email = c.PrimaryEmail });

var secondaryEmails = customerEmails
    .Where(ce => ce.CustomerId == 4)
    .Select(ce => new { ce.CustomerId, Email = ce.EmailId });

var combined = primaryEmails.Union(secondaryEmails).ToList();

// Aggregate to string
var aggregatedResult = combined
    .GroupBy(x => x.CustomerId)
    .Select(g => new 
    {
        CustomerId = g.Key,
        EmailIds = string.Join(" ", g.Select(x => x.Email))
    })
    .FirstOrDefault();

// Print the result
if (aggregatedResult != null)
{
    Console.WriteLine($"customerId:{aggregatedResult.CustomerId} EmailId: {aggregatedResult.EmailIds}");
}

Key Notes: #

• Use Concat() instead of Union() if you don't want to remove duplicates (equivalent to SQL's UNION ALL).
• Ensure the sequences you're combining have matching types/structures (in the examples, we project to an anonymous type with CustomerId and Email to make them compatible).

内容的提问来源于stack exchange，提问作者sumit.spider