如何正确实现两表筛选后关联查询并将结果存入List<cusPod>？

你的需求与现有代码 #

你需要从Customer和Product表中跳过首行，各取接下来的2条数据，关联后存入List<cusPod>。你写的代码思路是对的，但有几个关键细节需要调整，先看下你的原代码：

List<Customer> customers = db.Database.SqlQuery<Customer>("SELECT * FROM Customers ORDER BY CustomerId OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY").ToList();
List<Product> products = db.Database.SqlQuery<Product>("SELECT * FROM Products ORDER BY ProductId OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY").ToList();
var listSas = from p in products 
              join c in customers on p.ProductId equals c.CustomerId 
              select new { 
                  ProductId = p.ProductId, 
                  ProductName = p.ProductName, 
                  DateStart = p.DateStart, 
                  DateEnd = p.DateEnd, 
                  DateRegister = p.DateRegister, 
                  PriceCustomer = p.PriceCustomer, 
                  CheckPay = p.CheckPay, 
                  CustomerId = p.CustomerId, 
                  FIO = c.FIO, 
                  Email = c.Email, 
                  PhoneNumber = c.PhoneNumber 
              };
SidebarController.cusPod = listSas.ToList();

核心问题与修正方案 #

1. 关联条件大概率写反了 #

通常业务逻辑里，是Product表的CustomerId字段关联Customer表的CustomerId，而你写的on p.ProductId equals c.CustomerId逻辑上不太合理，除非你的业务有特殊设计。正确的关联条件应该是：

on p.CustomerId equals c.CustomerId

2. 匿名类型无法直接转成List<cusPod> #

你用匿名类型select new {...}生成的集合，不能直接赋值给List<cusPod>，必须直接实例化cusPod类：

select new cusPod { 
    ProductId = p.ProductId, 
    ProductName = p.ProductName, 
    DateStart = p.DateStart, 
    DateEnd = p.DateEnd, 
    DateRegister = p.DateRegister, 
    PriceCustomer = p.PriceCustomer, 
    CheckPay = p.CheckPay, 
    CustomerId = p.CustomerId, 
    FIO = c.FIO, 
    Email = c.Email, 
    PhoneNumber = c.PhoneNumber 
}

3. 可选：在数据库层面完成筛选+关联（更高效） #

如果你想减少内存中的集合操作，可以直接在SQL里完成筛选和关联，一次性拿到目标数据：

var listSas = db.Database.SqlQuery<cusPod>(@"
    SELECT 
        p.ProductId, 
        p.ProductName, 
        p.DateStart, 
        p.DateEnd, 
        p.DateRegister, 
        p.PriceCustomer, 
        p.CheckPay, 
        p.CustomerId, 
        c.FIO, 
        c.Email, 
        c.PhoneNumber 
    FROM (
        SELECT * FROM Products ORDER BY ProductId OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
    ) p
    JOIN (
        SELECT * FROM Customers ORDER BY CustomerId OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
    ) c ON p.CustomerId = c.CustomerId
").ToList();

SidebarController.cusPod = listSas;

这种方式更适合数据量较大的场景，减少应用端的内存消耗。

4. 确保cusPod类的属性匹配 #

要保证cusPod的属性名称和查询返回的列名完全一致（或者用[Column]特性做映射），比如你的cusPod类应该是这样的：

public class cusPod
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public DateTime DateStart { get; set; }
    public DateTime DateEnd { get; set; }
    public DateTime DateRegister { get; set; }
    public decimal PriceCustomer { get; set; }
    public bool CheckPay { get; set; }
    public int CustomerId { get; set; }
    public string FIO { get; set; }
    public string Email { get; set; }
    public string PhoneNumber { get; set; }
}

5. 数据库兼容性提醒 #

OFFSET/FETCH是SQL Server 2012+的语法，如果你的数据库是MySQL，需要改成LIMIT写法：

-- MySQL 跳过首行取2条
SELECT * FROM Customers ORDER BY CustomerId LIMIT 1, 2

修正后的完整代码（内存关联版） #

// 获取跳过首行的2条客户数据
List<Customer> customers = db.Database.SqlQuery<Customer>(
    "SELECT * FROM Customers ORDER BY CustomerId OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY"
).ToList();

// 获取跳过首行的2条产品数据
List<Product> products = db.Database.SqlQuery<Product>(
    "SELECT * FROM Products ORDER BY ProductId OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY"
).ToList();

// 关联并投影到cusPod类型
var listSas = from p in products 
              join c in customers on p.CustomerId equals c.CustomerId
              select new cusPod { 
                  ProductId = p.ProductId, 
                  ProductName = p.ProductName, 
                  DateStart = p.DateStart, 
                  DateEnd = p.DateEnd, 
                  DateRegister = p.DateRegister, 
                  PriceCustomer = p.PriceCustomer, 
                  CheckPay = p.CheckPay, 
                  CustomerId = p.CustomerId, 
                  FIO = c.FIO, 
                  Email = c.Email, 
                  PhoneNumber = c.PhoneNumber 
              };

// 赋值到静态列表
SidebarController.cusPod = listSas.ToList();

内容的提问来源于stack exchange，提问作者3uk Mat