C# query to fetch row based on foreign key

Question

I have two tables:

Component

and Component Profile

ComponentId is a foreign key to Component Profile

What I want to do is fetch All Component for a specific Business entity Id and then display all component Profiles for those Component ids from C#

I have been able to retrieve all components for a given business entity id

var component = context.Components.Where(i => i.BusinessEntityId == businessEntityId ).ToList();

I want to retrieve distinct component ids so that I can get all Component Profiles from it. How do i achieve this, Component Id is unique for each Profile. I am hoping that once I get distinct component Ids

foreach (var componentObject in componentObjects)
{
    var componentId = componentObject.Id;
    var componentProfile= context.ComponentProfiles.Where(i => i.ComponentId.Value == componentId);

    if (componentProfile != null)
    {
        result.Add(componentProfile.Map<Libraries.Entities.ComponentProfile>());
    }
}
               

What is an easy way to do this?

Answer 1

This solution combines all the code into a single Linq query.

var context = GetCoreDbEntityContext(businessEntityId);
var result = context.Components
    .Where(i => i.BusinessEntityId == businessEntityId)    // find relevant components
    .Select(c => new { c.ComponentId, c.BusinessEntityId })    // isolate the fields needed
    .Distinct()    // find distinct combinations of fields
    .Join(    // inner join distinct combinations with ComponentProfiles
        context.ComponentProfiles,    // table or list to inner join with
        c => c.ComponentId,    // key selector from Components used in join
        p => p.ComponentId,    // key selector from ComponentProfiles used in join
        (c, p) => new {    // select individual fields or table(s) as needed
            c.BusinessEntityId,    // individual component business entity ID
            c,    // all Component fields
            p    // all ComponentProfile fields
        })
    .Select(r => r.p)    // (optional) reduce columns to only ComponentProfiles
    .ToList();

return result;    // contains list of ComponentProfiles

Alternatively, if you only need the componentProfiles, you could do this.

var context = GetCoreDbEntityContext(businessEntityId);
var result = context.ComponentProfiles
    .Where(p => context.Components
        .Where(i => i.BusinessEntityId == businessEntityId)    // find relevant components
        .Any(c => c.ComponentId == p.ComponentId)    // include rows with matching ComponentId
    ).ToList();

return result;    // contains list of ComponentProfiles

Answer 2

There is a one-to-many relation between Profiles and Components:

• Every Component has exactly one Profile (using a foreign key with a rather confusing name. I'll use ProfileId);
• Every Profile may be used by zero or more Components.

Because one Profile may be used by several Components, there may be several 'Components' with the same foreign key value 'ProfileId`

I want to fetch All Component for a specific Business entity Id and then display all component Profiles for those Component ids

So given a businessEntityId, you need to find all Components with this businessEntityId. Once you've got these components, you can find the Profiles that belong to these components. As said, several Components may belong to the same Profile, so you'll have to use Distinct to remove duplicates.

var result = MyComponents
    // keep only the components with businessEntityId
    .Where(component => component.BusinessEntityId == businessEntitId)
    .Join(MyProfiles,                      // Join with Profiles
        component => component.ProfileId,  // from every component take the foreign key
        profile => profile.Id,             // from every profile take the primary key
        (component, profile) =>            // when they match
            profile)                       // keep the matching profile
    .Distinct()                            // and remove duplicates

Answer 3

This seemed to work out from all the pieces answered:

 try
        {
            var context = GetCoreDbEntityContext(businessEntityId);
            var components = context.Components.Where(i => i.BusinessEntityId == businessEntityId).ToList();
            var componentIdsDistinct = components.Select(c => c.Id).Distinct();

            foreach (var componentId in componentIdsDistinct)
            {
                var componentProfile = context.ComponentProfiles.SingleOrDefault(i => i.ComponentId == componentId);
                if (componentProfile != null)
                {
                    result.Add(componentProfile.Map<Libraries.Entities.ComponentProfile>());
                }
            }
            return result;
        }

Answer 4

I renamed your initial variable to be plural, since it returns multiple rows:

var components = context.Components.Where(i => i.BusinessEntityId == businessEntityId).ToList();

Now you can group your components by ComponentId so they are distinct:

var componentGroups = components.GroupBy(c => c.ComponentId);

Now you can combine the components that have each ComponentId with the ComponentProfile:

var componentProfiles = componentGroups.Select(cg => new { Components = cg.Select(c => c), Profile = context.ComponentProfiles.Single(cp => cp.ComponentId == cg.Key) });

So componentProfiles is an anonymous object that combines the list of Components rows with what I enforced is the single ComponentProfiles row that they use.

Answer 5

If I understand your question correctly, you can use Include.

var components = context.Components
     .Include("ComponentsProfiles") // I believe it is plural, but you need to check.
     .Where(i => i.BusinessEntityId == businessEntityId )
     .ToList();

You then loop through components, and get the corresponding component profiles.

foreach(var component in components) {
   var componentProfiles = component.ComponentsProfiles;
}

FYI: It is better to have unique identifier column in ComponentProfile table.