Entity Framework LINQ Get all items part of another collection

Question

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();

Shyju · Answer 1 · 2016-04-08T18:29:01.363

1

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;

edited Apr 08 '16 at 18:29

answered Apr 08 '16 at 18:18

Shyju

214,206
104
411
497

1

I don't think EF supports joins to inmemory collections. – Ivan Stoev Apr 08 '16 at 18:20
Slap an `AsEnumerable()` on the `db.NWatchRelations` and it should work, though performance could be an issue. – juharr Apr 08 '16 at 18:25
Absolutely correct @IvanStoev. Thanks for pointing out. @juhaar. Thanks. updated to include `AsEnumberale` ( I was going to put `.ToList()` :) ) – Shyju Apr 08 '16 at 18:30

score 1 · Answer 2 · answered Apr 08 '16 at 19:08

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.

score 0 · Answer 3 · answered Apr 08 '16 at 19:47

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.

score 0 · Answer 4 · answered Apr 08 '16 at 22:49

Also you can have the directly linked like this

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public NWatchRelation()
    {
        this.INWatchRelation = new HashSet<INWatchRelation>();
    }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<INWatchRelation> INWatchRelation { get; set; }

But the entiry relation must be liked like this in order to work properly

Then you could select/list it like this

db.NWatchRelation.INWatchRelation.ToList();

Entity Framework LINQ Get all items part of another collection

4 Answers4

Linked