CODEWITHSUNDEEP
c#linqentity-framework
Wjdavis5
Wjdavis5

Reputation: 4151

EF and Linq Except

Given these two EF models - how would I use linq to select all records from InteractionSegmentDetail that do not exist in Custom_SegmentsParsed? Using the InteractionIDKey field as the comparer.

 public partial class Custom_SegmentsParsed
    {
        public string InteractionIDKey { get; set; }
        public string SegmentNum { get; set; }
        public System.DateTime ServerTs { get; set; }
        public System.DateTime CUTC { get; set; }
        public Nullable<int> Duration { get; set; }
        public string LocalParty { get; set; }
        public string Queue { get; set; }
        public string EndCode { get; set; }
        public string Details { get; set; }
}
  public partial class InteractionSegmentDetail
    {
        public string InteractionIDKey { get; set; }
        public short SiteID { get; set; }
        public byte SeqNo { get; set; }
        public System.DateTime StartDateTimeUTC { get; set; }
        public int StartDTOffset { get; set; }
        public string ConversationID { get; set; }
        public string SegmentLog { get; set; }
}

Upvotes: 4

Views: 3598

Answers (4)

win32
win32

Reputation: 1

here's an option that performs a left-join using the query syntax that will be efficient:

var results = 
    (from isd in db.InteractionSegmentDetails
     join csp in db.Custom_SegmentsParsed on isd.InteractionIDKey equals csp.InteractionIDKey into possibleCSPs
     from csp in possibleCSPs.DefaultIfEmpty()
     where csp.InteractionIDKey == null //ensure no CSP match was found
     select isd).Distinct().ToArray(); //don't include duplicates (just in case)

Upvotes: 0

jimSampica
jimSampica

Reputation: 12410

I would use Where and Any

isdList.Where(isd => !cspList
                     .Any(csp => isd.InteractionIDKey == csp.InteractionIDKey));

Upvotes: 4

Travis J
Travis J

Reputation: 82267

I would use three queries here as combining them, in this instance is going to ruin performance.

List<string> customKeys = dbContext.Custom_SegmentsParseds.Select(s => s.InteractionIDKey).ToList();
List<string> interactionKeys = dbContext.InteractionSegmentDetails.Select(s => s.InteractionIDKey).ToList();
IEnumerable<string> overLap = interactionKeys.Except(customKeys);
List<InteractionSegmentDetail> detailList = dbContext.InteractionSegmentDetails.Where(seg => overLap.Contains(seg.InteractionIDKey)).ToList();

Upvotes: -1

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

dbContext.InteractionSegmentDetail
         .Where(isd => !dbContext.Custom_SegmentsParsed
                                 .Select(x => x.InteractionIDKey)
                                 .Contains(isd.InteractionIDKey));

Upvotes: 3

Related Questions