Combining Objects into a new model with Linq/Lambda expressions-CodePudding

I am working on a Blazor Project and using Dapper to Pull Data from a SQL Db. I am pulling 3 tables at the moment. An entity table, a specialty table and a bridge table that is there to maintain the many to many relationship between entity and specialties.

I am able to pull from SQL fine and I want to combine the data in my data service and inject it as a new object model to the Blazor component.

Here are the models:

Entity

public class EntityModel : IEntityModel
    {
        public int Id { get; set; }
        public int PhysicianId { get; set; }
        public int PartnerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

Specialty

 public class SpecialtyModel : ISpecialtyModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

BridgeModel

public class BridgeModel : IBridgeModel
    {
        public int Id1 { get; set; }
        public int Id2 { get; set; }
    }

I made the properties in the bridge model generic so I could use it with another bridge table I have for a many to many relationship. The bridge tables are just two columns of IDs that link their respective tables. In this case Entity.Id and Specialty.Id

Here is the model I am combining all the information into:

 public class CombinedModel : ICombinedModel
    {
        public int Id { get; set; }
        public int PhysicianId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public List<ISpecialtyModel> Specialties { get; set; }
    }

Here is the inside of my data service where I am stuck trying to combine the data with Linq and Lambda expressions.

 public async Task<List<IEntityModel>> ReadEntities()
        {
            var entities = await _dataAccess.LoadData<EntityModel, dynamic>("dbo.spEntity_Read", new { }, "SqlDb");
            return entities.ToList<IEntityModel>();
        }

        public async Task<List<ISpecialtyModel>> ReadSpecialties()
        {
            var specialties = await _dataAccess.LoadData<SpecialtyModel, dynamic>("dbo.spSpecialty_Read", new { }, "SqlDb");
            return specialties.ToList<ISpecialtyModel>();
        }

        public async Task<List<IBridgeModel>> ReadEntitySpecialtyBridge()
        {
            var bridge = await _dataAccess.LoadData<BridgeModel, dynamic>("dbo.spEntitySpecialty_Read", new { }, "SqlDb");
            return bridge.ToList<IBridgeModel>();
        }

        public async Task<List<ICombinedModel>> CombineData()
        {
            var entities = await ReadEntities();
            var specialties = await ReadSpecialties();
            var bridge = await ReadEntitySpecialtyBridge();

            //var combined = (from e in entities
            //                join b in bridge on e.Id equals b.Id1
            //                join s in specialties on b.Id2 equals s.Id
            //                select new CombinedModel()
            //                {
            //                    Id = e.Id,
            //                    PhysicianId = e.PhysicianId,
            //                    FirstName = e.FirstName,
            //                    LastName = e.LastName,
            //                    Specialties = new List<ISpecialtyModel>()
            //                });

            var combined = (from e in entities
                            select new CombinedModel
                            {
                                Id = e.Id,
                                PhysicianId = e.PhysicianId,
                                FirstName = e.FirstName,
                                LastName = e.LastName,
                                Specialties = specialties.Where(s =>  )
                            }
                            );

            return combined.ToList<ICombinedModel>();

This is where I am stuck. How can I write this Linq query to combine this data into the new model?

I am able to get data passed into the razor component but I am not combining it correctly and this is where I am stuck.

I hope someone can shed some light on the matter. Thank you for taking the time to look over this, I appreciate it.

With Thanks, Cesar

CodePudding user response：

If you wanted to process locally (doing on the server should eliminate the need to pull bridge over from the database, and if bridge contains records that aren't relevant to entities could potentially be a lot of unnecessary data traffic) then you just need to filter specialties by the correct bridge records for a given entity:

var combined = (from e in entities
                select new CombinedModel {
                    Id = e.Id,
                    PhysicianId = e.PhysicianId,
                    FirstName = e.FirstName,
                    LastName = e.LastName,
                    Specialties = specialties.Where(s => bridge.Where(b => b.Id1 == e.Id).Select(b => b.Id2).Contains(s.Id)).ToList()
                });

Depending on the size of specialties and entities, it might be worthwhile to pre-process bridge to make access for a given entity more efficient (Where is O(n) so specialties.Where x bridge.Where is O(n*m)):

var bridgeDict = bridge.GroupBy(b => b.Id1).ToDictionary(bg => bg.Key, bg => bg.Select(b => b.Id2).ToHashSet());
var combined = (from e in entities
                let eBridge = bridgeDict[e.Id]
                select new CombinedModel {
                    Id = e.Id,
                    PhysicianId = e.PhysicianId,
                    FirstName = e.FirstName,
                    LastName = e.LastName,
                    Specialties = specialties.Where(s => eBridge.Contains(s.Id)).ToList()
                });