LINQ - Join with List

.net c# entity-framework-core linq

Question

I have condition where i have to join two different types of array to form a new array.

Let's see the class where condition should be created.

public class MutualFundParameter
{
    public int Id { get; set; }
    public string AcNo { get; set; }
    public string CustomerName { get; set; }
}

public class VoucherDetails
{
    public int Id { get; set; }
    public int AcNo { get; set; }
    public decimal Amount { get; set; }
    public int MasterId { get; set; }
}

public class VoucherMaster
{
    public int Id { get; set; }
    public DateTime DateTime { get; set; }
    public ICollection<VoucherDetails> VoucherDetails { get; set; }
}

New let's add some data into that object

// mutual fund parameter
var mutualFundParameters = new List<MutualFundParameter>
{
    new MutualFundParameter { Id = 1, AcNo = "001", CustomerName = "Ram" },
    new MutualFundParameter { Id = 2, AcNo = "002", CustomerName = "Shyam" },
    new MutualFundParameter { Id = 3, AcNo = "003", CustomerName = "Hari" },
    new MutualFundParameter { Id = 4, AcNo = "004", CustomerName = "Gita" },
    new MutualFundParameter { Id = 5, AcNo = "005", CustomerName = "Sita" },
};

// voucher details
var voucherDetails = new List<VoucherDetails>
{
    new VoucherDetails { Id = 1, AcNo = 001, Amount = 10.00M, MasterId = 1 },
    new VoucherDetails { Id = 2, AcNo = 001, Amount = 120.00M, MasterId = 1 },
    new VoucherDetails { Id = 3, AcNo = 002, Amount = 20.00M, MasterId = 1 },
    new VoucherDetails { Id = 4, AcNo = 002, Amount = 120.00M, MasterId = 1 },
    new VoucherDetails { Id = 5, AcNo = 002, Amount = 30.00M, MasterId = 1 },
    new VoucherDetails { Id = 6, AcNo = 002, Amount = 20.00M, MasterId = 1 },
    new VoucherDetails { Id = 7, AcNo = 003, Amount = 20.00M, MasterId = 1 },
    new VoucherDetails { Id = 8, AcNo = 003, Amount = 20.00M, MasterId = 1 },
    new VoucherDetails { Id = 9, AcNo = 003, Amount = 20.00M, MasterId = 1 },
    new VoucherDetails { Id = 10, AcNo = 004, Amount = 10.00M, MasterId = 2 },
    new VoucherDetails { Id = 11, AcNo = 005, Amount = 20.00M, MasterId = 2 },
    new VoucherDetails { Id = 12, AcNo = 006, Amount = 30.00M, MasterId = 2 },
    new VoucherDetails { Id = 13, AcNo = 007, Amount = 40.00M, MasterId = 2 },
    new VoucherDetails { Id = 14, AcNo = 008, Amount = 50.00M, MasterId = 2 },
    new VoucherDetails { Id = 15, AcNo = 009, Amount = 60.00M, MasterId = 2 },
    new VoucherDetails { Id = 16, AcNo = 001, Amount = 70.00M, MasterId = 2 },
};

// voucher
var voucherMasters = new List<VoucherMaster>
{
    new VoucherMaster
    {
        Id = 1,
        VoucherDetails = voucherDetails
                           .Where(x => x.MasterId == 1)
                           .ToList(),
        DateTime = DateTime.Now }
};

Now i need to join MutualFundParameter with VoucherDetails but that is inside a list of 'Voucher Master' To join i have tried this but this doesn't work as expected

var res = voucherMasters.Join(
          mutualFundParameters,
          voucher => voucher.VoucherDetails.FirstOrDefault().AcNo,
          mutual => mutual.AcNo,
          (voucher, mutual) => new
          {

          }
);

Thanks Hoping for positive response.

1
0
9/2/2019 6:18:59 PM

Accepted Answer

Assuming that AcNo is of the same type in both mutualFundParameter and voucherDetail, here are two examples querying either in-memory collection or db: (Note: I've not compiled/run the code so please excuse any syntactical errors)

var resultFromInMemory = (
        from vd in voucherMasters.SelectMany(x => x.voucherDetails)
        join mfp in mutualFundParameters on mfp.AcNo equals vd.AcNo
        select new { vd, mfp });

var resultFromDb = (
        from vd in db.voucherDetails
        join mfp in db.mutualFundParameters on mfp.AcNo equals vd.AcNo
        select new { vd, mfp });

For in-memory collection since you're expecting voucherDetails to be a child collection of voucherMaster, performing a join between mutualFundParameters and voucherDetails requires that voucherDetails is first consolidated into a temporary single collection which .SelectMany helps with

In case of database (assuming relational db) no such intermediate collection would be needed as every entity is a flat table in there.

0
9/2/2019 6:29:10 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow