I have my 2 models like below
public class FItem
{
public FItem() { }
public int RecordId { get; set; }
public int MarketId { get; set; }
public string ItemName { get; set; }
public string ItemFamily { get; set; }
public string HoverFamily { get; set; }
public string ItemDesc { get; set; }
public IEnumerable<FSubsystem> FSubsystems { get; set; }
}
public class FSubsystem
{
public FSubsystem() { }
public int FSubsystemId { get; set; }
public int RecordId { get; set; } //Foreign key
public int supplierId { get; set; }
public int SubSystemTypeId { get; set; }
public double Percentage { get; set; }
public double? Value { get; set; }
}
public class FReferences
{
public FReferences() { }
public int RecordID { get; set; } //Foreign key
public int SourceID { get; set; }
public DateTime SourceDate { get; set; }
public string Reference { get; set; }
public int? ReferenceID { get; set; }
}
and I use dapper to fetch the data and put into objects . code is as belolw
using (var multi = mapperConnection.QueryMultiple("USP_FetchMarketRecords", parameters, (SqlTransaction)null, 1000000, CommandType.StoredProcedure))
{
IEnumerable<MarketRecord.FItem> FItem = multi.Read<MarketRecord.FItem>().ToList();
IEnumerable<MarketRecord.FSubsystem> FSubsystem = multi.Read<MarketRecord.FSubsystem>().ToList();
}
Now I want to get the subsystems for each record id and put them in FSubsystems property of Fitem . How can I do this ?
Here I am showing only one one to many relationship to FItem thats Fsubsystem . But I have many one to many tables to Fitem like FReferenc ,FUnit etc . For all foreign key is RecordId itelf.
Can this be done through linq query ? or should I use some diff technique ?
Dapper doesn't include anything built in to reconstruct parent/child relationships from different sets.
You can probably generalize the scenario something like:
static void ApplyParentChild<TParent, TChild, TId>(
this IEnumerable<TParent> parents, IEnumerable<TChild> children,
Func<TParent, TId> id, Func<TChild, TId> parentId,
Action<TParent, TChild> action)
{
var lookup = parents.ToDictionary(id);
foreach (var child in children)
{
TParent parent;
if (lookup.TryGetValue(parentId(child), out parent))
action(parent, child);
}
}
so if we had:
List<Parent> parents = new List<Parent> {
new Parent { Id = 1 },
new Parent { Id = 2 }
};
List<Child> children = new List<Child> {
new Child { Id = 3, ParentId = 1},
new Child { Id = 4, ParentId = 2},
new Child { Id = 5, ParentId = 1}
};
You could use:
parents.ApplyParentChild(children, p => p.Id, c => c.ParentId,
(p,c) => p.Children.Add(c));