LINQ troubles in C# using Entity Framework-CodePudding

I have a few tables and this is what I need to achieve.

This gets all the rows from one table

var FRA = from prod in _cctDBContext.Fra 
          where prod.ActTypeId == 1

From within that, I get all the rows where ActTypeID.

Then I need to query another table from with the ID's get from that

foreach (var item in FRA)
{
    var FRSA = _cctDBContext.Frsa
                            .Select(p => new { p.Fraid, p.Frsa1, 
                                               p.Frsaid, p.CoreId, 
                                               p.RelToEstId, p.ScopingSrc,
                                               p.Mandatory })
                            .Where(p => p.Fraid == item.Fraid)
                            .ToList();
}

I then need to push each one of these to Entity Framework. I usually do it this way:

foreach (var item in FRA)
{
    var FinanicalReportingActivity = new FinancialReportingActivity { FinancialReportingActivityId = item.Fraid, ScopingSourceType = item.ScopingSrc, Name = item.Fra1, MandatoryIndicator = item.Mandatory, WorkEffortTypeId = 0 };
    _clDBContext.FinancialReportingActivity.AddRange(FinanicalReportingActivity);
}

But because I have used 2 for each loops, I cannot get the variables to work because I cannot find a way to get local variables as the entity context.

Can anyone think of a better way to code this?

Thanks

CodePudding user response：

It looks like you can do this as a single join:

var query =
    from prod in _cctDBContext.Fra
    where prod.ActTypeId == 1
    join p in _cctDBContext.Frsa on prod.Fraid equals p.Fraid
    select new
    {
        p.Fraid,
        p.Frsa1,
        p.Frsaid,
        p.CoreId,
        p.RelToEstId,
        p.ScopingSrc,
        p.Mandatory
    };

CodePudding user response：

It looks like you are loading data from one set of entities from one database and want to create matching similar entities in another database.

Navigation properties would help considerably here. Frsa appear to be a child collection under a Fra, so this could be (if not already) wired up as a collection within the Fra entity:

Then you only need to conduct a single query and have access to each Fra and it's associated Frsa details. In your case you look to be more interested in the associated FRSA details to populate this ReportingActivity:

var details = _cctDBContext.Fra 
    .Where(x => x.ActTypeId == 1)
    .SelectMany(x => x.Frsa.Select(p => new 
    {
         p.Fraid, 
         p.Frsa1, 
         p.Frsaid, 
         p.CoreId, 
         p.RelToEstId, 
         p.ScopingSrc,
         p.Mandatory 
    }).ToList();

though if the relationship is bi-directional where a Fra contains Frsas, and a Frsa contains a reference back to the Fra, then this could be simplified to:

var details = _cctDBContext.Frsa 
    .Where(x => x.Fra.ActTypeId == 1)
    .Select(p => new 
    {
         p.Fraid, 
         p.Frsa1, 
         p.Frsaid, 
         p.CoreId, 
         p.RelToEstId, 
         p.ScopingSrc,
         p.Mandatory 
   }).ToList();

Either of those should give you the details from the FRSA to populate your reporting entity.