C# Multiple Generic List<t> - Combining Them?!

Question

Scenario:

I have a generic list of Audits and a generic list of AuditImages. These two lists have been compiled from database tables. As a result of this, ONE AuditImage can have MANY Audits. As you will see below, the classes that the tables map to are joined by a foreign key relationship "ImageID" when they are in the database, however once the data is extracted to lists in code, there is no "PHYSICAL JOIN".

Classes That DB Tables Map To:

public class AuditImage
{
    public Guid ImageID { get; set; }
    public string LowResUrl { get; set; }
}

public class Audit
{
    public Guid AuditID { get; set; }
    public Guid ImageID { get; set; }
    public DateTime CreatedDate { get; set; }
    public string CreatedBy { get; set; }
    public string Comment { get; set; }
}

The Problem:

I now want to compile a list of "Trail" objects by extracting the data from each list and combining it on "Audit.ImageID == AuditImage.ImageID", into a new list.

public class Trail
{
    public Guid ImageID { get; set; }
    public DateTime CreatedDate { get; set; }
    public string CreatedBy { get; set; }
    public string Comment { get; set; }
    public string LowResUrl { get; set; }
}

(The above essentially combines the "LowResUrl" field with each Audit based on the ImageID being the same.)

The Question:

How should I go about doing this!? I had thought about using foreach loops and linq to create a new list of trail objects but I can't quite think of exactly how I would go about doing this?!

Help would be greatly appreciated.

Answer 1

See Here on SO

Answer 2

Try the Intersect extension method on List: http://msdn.microsoft.com/en-us/library/bb910215.aspx

I also think you should review your design and introduce a common interface IAudit or something similar.

Answer 3

you could do

var trails = from audit in audits
   join image in auditImages on audit.ImageId equals image.ImageId
   select new Trail { ImageID = audit.ImageId, CreatedDate = audit.CreatedDate,
                      CreatedBy = audit.CreatedBy, Comment = audit.Comment,
                      LowResUrl = image.LowResUrl };

Answer 4

This could work.......

public List<JobImageAudit> CombineForAuditTrail()
        {
            var result = from a in auditList
                         join ai in imageList
                         on a.ImageID equals ai.ImageID
                         //into ait // note grouping        
                         select new JobImageAudit
                         {
                             JobID = a.JobID,
                             ImageID = a.ImageID.Value,
                             CreatedBy = a.CreatedBy,
                             CreatedDate = a.CreatedDate,
                             Comment = a.Comment,
                             LowResUrl = ai.LowResUrl,

                         };
            return result.ToList();
        }

Answer 5

I would probably use the extension methods, just because I'm not very ease into linq:

IEnumerable<Trail> trailList = auditImageList.Join(
  auditList, 
  auditImageItem => auditImageItem.ImageId, 
  auditItem => auditItem.ImageId,
  (auditImageItem, auditItem) =>  new Trail() 
  {  
    LowResUrl = auditImageItem.LowResUrl,
    ImageID = auditImageItem.ImageId, 
    CreatedDate = auditItem.CreatedDate,
    CreatedBy = auditItem.CreatedBy, 
    Comment = auditItem.Comment,
  });

Answer 6

This will solve the issue.. from var query just add to trail objects in loop

    var query = from auditImage in AuditImageList 
    join audit in AuditList 
on auditImage.ImageID equals audit.ImageID 
select new { ImageID= auditImage.ImageID, CreatedDate = audit.CreatedDate, CreatedBy = audit.CreatedBY, Comment = audit.Comment , LowResUrl = auditImage.LowResUrl }; 

foreach (var trail in query) { //Assign Values to trail object and add to list of trails }

Answer 7

Depends on what you want:

Inner join (only display AuditImage if it has a Audit):

var innerJoin = from image in images
                join audit in audits on image.ImageID equals audit.ImageID
                select new { image.ImageID, AuditImageId = audit.ImageID };

Left join (display AuditImage even if it does not have a Audit):

var leftJoin = from image in images
               join audit in audits on image.ImageID equals audit.ImageID
               into auditCats
               from auditCat in auditCats.DefaultIfEmpty(new Audit())
               select new { image.ImageID, AuditImageId = auditCat.ImageID };

Answer 8

There are at least two options.

First is do simple join:

var trails = Audits.Join(Images, a => a.ImageID, i => i.ImageID, (a, i) =>
    new Trail
    {
        Comment = a.Comment,
        CreatedBy = a.CreatedBy,
        CreatedDate = a.CreatedDate,
        ImageID = a.ImageID,
        LowResUrl = i.LowResUrl
    });

Second is to use sub-query:

var trails = Audits.Select(a =>
    new Trail
    {
        Comment = a.Comment,
        CreatedBy = a.CreatedBy,
        CreatedDate = a.CreatedDate,
        ImageID = a.ImageID,
        LowResUrl = Images.Single(i => i.ImageID == a.ImageID).LowResUrl
    });

Both should work fine if ImageID is unique but I personally like sub-query more because it looks more natural than join.