从两个不同的表中以linq union方式获取选定的记录

Question

我有两个表，UserNotifications和GlobalNotifications。我需要Union这两个表在一起和日期顺序他们检索用户通知历史。这是我目前的工作查询:

var q = db.UserNotifications.Where(c => c.UserID == forUser.ScirraUserID)
    .Select(c => new {c.ID, type = "u", date = c.FirstDate})
    .Union(db.GlobalNotifications.Select(c => new {c.ID, type = "g", date = c.Date}))
    .OrderBy(c => c.date)
    .Skip(skip)
    .Take(take);

问题是，我无法在结果中返回UserNotifications和GlobalNotifications记录，因为应该只是它们的类型和ID，这将需要对数据库进行另一次查询来检索。

Answer 1

public enum NotificationType { User, Global }
public class mytype {
public int ID {get;set;}
public NotificationType type {get;set;}
public DateTime FirstDate {get;set;}
public UserNotification un {get;set;}
public GlobalNotification gn {get;set;}
}
var q = db.UserNotifications.Where(c => c.UserID == forUser.ScirraUserID)
    .Select(c => new mytype {c.ID, type = NotificationType.User, date = c.FirstDate, un=c, gn=null})
    .Union(db.GlobalNotifications.Select(c => new mytype {c.ID, type = NotificationType.Global, date = c.Date, un=null, gn=c }))
    .OrderBy(c => c.date)
    .Skip(skip)
    .Take(take);

或者您可以直接测试un,gn中哪个为空，然后跳过类型，如:

var q = db.UserNotifications.Where(c => c.UserID == forUser.ScirraUserID)
    .Select(c => new mytype {c.ID, date = c.FirstDate, un=c, gn=null})
    .Union(db.GlobalNotifications.Select(c => new mytype {c.ID, date = c.Date, un=null, gn=c }))
    .OrderBy(c => c.date)
    .Skip(skip)
    .Take(take);

或者如果UserNotification和GlobalNotification都有一个共同的祖先(Notification)或接口(INotification?)，那么可能使用并强制转换到那个。

如果您正在使用LINQ2SQL，那么您可能需要这样做:

var q = db.UserNotifications
    .Where(c => c.UserID == forUser.ScirraUserID)
    .ToList()
    .Select(c => new mytype {
        c.ID, 
        type = NotificationType.User, 
        date = c.FirstDate, 
        un=c, 
        gn=null})
    .Concat(
        db.GlobalNotifications
            .Select(c => new mytype {
                c.ID, 
                type = NotificationType.Global, 
                date = c.Date, 
                un=null, 
                gn=c })
            .ToList())
    .OrderBy(c => c.date)
    .Skip(skip)
    .Take(take);

尝试2:

var q1 = db.UserNotifications
    .Where(c => c.UserID == forUser.ScirraUserID)
    .OrderBy(c => c.FirstDate)
    .Select(c => new mytype {
        c.ID, 
        type = NotificationType.User, 
        date = c.FirstDate, 
        un=c, 
        gn=null})
    .Take(skip+take)
    .ToList();
var q2=db.GlobalNotifications
    .OrderBy(c => c.Date)
    .Select(c => new mytype {
        c.ID, 
        type = NotificationType.Global, 
        date = c.Date, 
        un=null, 
        gn=c })
    .Take(skip+take)
    .ToList();
var r=q1.Concat(q2)
    .OrderBy(c => c.FirstDate)
    .Skip(skip)
    .Take(take);