LINQ troubles in C# using Entity Framework

Question

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

The FRSA is where I get the id from and I need to query another table with that Id and then push that data to the entity framework … that’s basically what I am after … do you think using a sql query within linq would be the answer , so I can roll up both operations without the need for a for each loop ?? — Jason_Hough
– Jason_Hough, Commented Sep 2, 2021 at 18:44
Your second foreach doesn't seem to relate at all to the first one. Can you provide a minimal reproducible example? — Enigmativity
– Enigmativity, Commented Sep 2, 2021 at 23:05

Enigmativity · Accepted Answer · 2021-09-02 23:03:38Z

1

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
    };

answered Sep 2, 2021 at 23:03

Enigmativity

117k12 gold badges101 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve Py · Accepted Answer · 2021-09-02 22:54:50Z

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.

Collectives™ on Stack Overflow

LINQ troubles in C# using Entity Framework

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related