CODEWITHSUNDEEP
c#linqentity-framework-6
Songaila
Songaila

Reputation: 219

How to join two LINQ queries

I have two related entities with composite keys:

public class Event
{
    public string ID1 { get; set; }
    public int ID2 { get; set; }
    public DateTime EventDate { get; set; }
    public string EventData { get; set; } 
    public string DocID1 { get; set; }
    public int DocID2 { get; set; }
}

public class EventDocument
{

    public string ID1 { get; set; }
    public int ID2 { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Number { get; set; }

    public virtual ICollection<Event> Events { get; set; }
}

Is there a possibility to filter first both of them by some criteria and then to join results because of big amount of records?

Acctually I can reach related Events when I filter EventDocuments, but I also need a possibility to filter Event and EventDocument in one time.

I am trying to do like this:

var events = ModelContext.Events.AsNoTracking().Select(x => x);
events = events.Where(x => x.EventData.StartsWith(FilterCriteria));


var eventDocuments = ModelContext.EventDocuments.AsNoTracking().Select(x => x);
eventsDocuments = eventDocuments.Where(x => x.LastName.StartsWith(FilterLastName));

And now I need to join these to queries and get a result - filtered and joined data from two entities

Trying to do like this:

var result = eventDocuments.Join(events, 
                        doc => new { doc.ID1, doc.ID2 }, 
                        ev => new { cross.DocID1, cross.DocID2}, 
                        (doc, ev) => new { EventDocument = doc, Event = ev });

Upvotes: 3

Views: 10961

Answers (1)

Sefe
Sefe

Reputation: 14007

You can simply query both sets with SelectMany. In query syntax this would look like:

var eventsQry =
    from eventDocument in eventDocuments
    where eventDocument.LastName.StartsWith(FilterLastName)
    from ev in events
    where ev.EventData.StartsWith(FilterCriteria) && (ev.ID1 == eventDocument.ID1) && (ev.ID2 == eventDocument.ID2)
    select new { eventDocument, ev };

You don't need to use one query to filter your results. You can combine multiple queries:

var eventsQry =
    from ev in events
    where ev.EventData.StartsWith(FilterCriteria)
    select ev

var documentsQry =
    from eventDocument in documentsQry
    where eventDocument.LastName.StartsWith(FilterLastName)
    select eventDocument;

var combinedQry =
    from eventDocument in documentsQry
    from ev in eventsQry
    where (ev.ID1 == eventDocument.ID1) && (ev.ID2 == eventDocument.ID2)
    select new { eventDocument, ev };

Upvotes: 2

Related Questions