How to combine two lists into one Model by an Id Key field?

Question

I have a model that looks like this

public class ReferralModel
{
    public string? ClientName { get; set; }
    public Guid? ClientId { get; set; }
    public string? ClientNumber { get; set; }
    public string? ClientDOB { get; set; }
    public string? DateSubmitted { get; set; } = default;
    public DateTime? ModifiedDateSubmitted { get; set; }
    public string? ReportStatus { get; set; }
    public string? ReferralNotes { get; set; }
    public ReferralForm? referralForm { get; set; }
}

public class ReferralForm
{
    public Guid FileId { get; set; }
    public Guid ClientId { get; set; }
    public string? Name { get; set; }
    public string? FileName { get; set; }
    public string? ContentType { get; set; }
    public string? FileExtension { get; set; }
    public byte[]? FileContent { get; set; }
    public DateTime CreatedDate { get; set; }
}

I have two separate lists that both have the same ClientId.

var referrals = new List<ReferralModel>();
var referralForms = new List<ReferralForm>();

I want to add the referral forms list to its corresponding ReferralList so that each referral list will have the correct Referral form (ReferralForm? referralForms).

referrals.referralForm = referralForms.Where(x => x.ClientId == referrals.ClientId);

I thought about looping through each of the list of referrals, but the list could be in the thousands and that seems like it would take too much time. This has to be almost instant mapping

Where are these lists coming from? Sounds like the mapping should be done at the origin. — funatparties, Dec 24 '22 at 22:32
You should consider turning `referralForms` into a `Dictionary` first, to faster read it. So `var dict = referralForms.ToDictionary(x => x.ClientId); referrals.referralForm = dict[referrals.ClientId];` — Rand Random, Dec 24 '22 at 22:35
`Dictionary` as @RandRandom suggested. And also check if you can utilize Parallel LINQ https://learn.microsoft.com/en-us/dotnet/standard/parallel-programming/introduction-to-plinq for this . If you could paralelize it, it would decrease the total time — E. Shcherbo, Dec 24 '22 at 22:41
I would probably just use `IQueryable.Join` or `IEnumerable.Join` on this; I can't speak much to performance with this kind of information. is this data already in memory and is in a list collection? are you going to be caching it for subsequent queries? — Brett Caswell, Dec 25 '22 at 00:11

Brett Caswell · Answer 1 · 2022-12-25T02:34:14.107

0

I'll provide a general approach here -- using an Extension Method LeftOuterJoin (in respect to Enumerable.Join<TOuter,TInner,TKey,TResult>, but as LEFT OUTER JOIN behavior)

It would look something like this:

var models = GetModels().LeftOuterJoin(GetForms(), 
  (outter) => outter.ClientId, 
  (inner) => inner.ClientId, 
  (outter, inner) => {
    outter.referralForm = inner;
    return outter
  });

dotnetFiddle

Edit

replacing Enumerable.Join with Extension LeftOuterJoin usage -- as referenced in popular SO Answer https://stackoverflow.com/a/39020068/1366179

edited Dec 25 '22 at 02:34

answered Dec 25 '22 at 01:28

Brett Caswell

1,486
1
13
25

In regard to performance considerations, I don't necessarily want to make it a consideration to this question and answer -- it can get pretty nuance. concrete types of the collections are an important factor for read/write operations. Also, whether or not is has a unique key and whether or not the data is sorted. In respect to doing sequential joins of two collections, I want to say a `SortedDictionary`(where the Key is ClientId (Guid)) may be best/quickest to join/combine due O(1) lookups; However, you have to still instantiate the collection. – Brett Caswell Dec 25 '22 at 01:30
Since there is an equality check on the key and positioning overhead when writing/adding/instantiating such a collection, a larger dataset may take some time to instantiate before reading. note that EntityFramework uses `HashSet`, which is unsorted but unique. And that may be your best overall bet here for a concrete, collection type that's quicker to instantiate then a SortedDictionary and still fast to read. It also specifically implements `IEnumerable`, so this provided, general answer using Joins is applicable. – Brett Caswell Dec 25 '22 at 01:32
geesh, forget that `Join` is `InnerJoin` behavior. I referenced to an SO provided answer that defines an extension method, but there is probably an extension method in MoreLinq that'll make for a better answer/alternative here to defining this extension method. sorry for the confusion. – Brett Caswell Dec 25 '22 at 02:04

How to combine two lists into one Model by an Id Key field?

1 Answers1

Edit