实体框架linq获取所有项目的所有部分[英] Entity Framework LINQ Get all items part of another collection

问题描述

Get all the NWatchRelation records from the DBContext that overlap those in the relationsCollection. The same Id, RelatedNodeId, and RelationType (enum: int) should be what's considered a match.

public class NWatchRelation : INWatchRelation
{
    public int Id { get; set; }
    public int NodeId { get; set; }
    public NWatchNode Node { get; set; }
    public int RelatedNodeId { get; set; }

    public NWatchNode RelatedNode { get; set; }
    public NWatch.NWatchRelationType RelationType { get; set; }
}

INWatchRelation[] relationsCollection = GetRelations();

推荐答案

You can do a LINQ join between these 2 collections.

var result = from a in db.NWatchRelations.AsEnumerable()
             join b in relationsCollection on a.RelatedNodeId equals b.RelatedNodeId
                                           && a.Id equals b.Id
                                           && a.RelationType equals b.RelationType 
             select a;

其他推荐答案

The only way you can do that fully in LINQ to Entities is to manually compose UNION ALL query by using Queryable.Concat like this:

IQueryable<NWatchRelation> query = null;
foreach (var relation in relationsCollection)
{
    var m = relation;
    var subQuery = db.NWatchRelations
        .Where(r => r.Id == m.Id
            && r.RelatedNodeId == m.RelatedNodeId
            && r.RelationType == m.RelationType);
    query = query == null ? subQuery : query.Concat(subQuery);
}

But please note that it's a limited approach and will not work if the relationsCollection is big.

其他推荐答案

You could create a kind of unique key using the three values:

//To create a unique key (an string, which is a primitive type) combining the three values
var keys=relationsCollection.Select(e=>e.Id+"-"+e.RelatedNodeId+"-"+ ((int)e.RelationType)).Distinct();

var query=db.NWatchRelations.Where(r=>keys.Any(k=>k == (SqlFunctions.StringConvert((double)r.Id)+"-"+
                                                        SqlFunctions.StringConvert((double)r.RelatedNodeId )+"-"+ 
                                                        SqlFunctions.StringConvert((double)((int)r.RelationType)) ));

If your NWatchRelations table doesn't have many rows or relationsCollection is a small collection, please, use one of the alternatives that were proposed earlier at your convinience.