Click here to Skip to main content
15,851,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I've 3 tables:
Company(CompanyID, CompanyName)
Executives(ExecutiveID, ExecutiveName)
CompanyExecutives(CompanyID, ExecutiveID, ExecutiveType, DateAppointed, DateResignation)

Using my EDM I am setting my Datagridview.Datasource =

C#
public ObjectResult<CompanyExecutives> GetCompanyExecutivesList()
{
    // Check we have an ObjectContext
    if (entities == null) entities = new CompanySecretaryEntities();

    // Create a query from the entityset
    ObjectQuery<CompanyExecutives> companyexecutives = entities.CompanyExecutives;

    // Return the results
    return companyexecutives.Execute(MergeOption.AppendOnly);
}



But this is only populating my Datagridview with the CompanyID, ExecutiveID and DateAppointed. I'd like to display the CompanyName in place of CompanyID, ExecutiveName in place of ExecutiveID. Can anyone suggest how I should do this? I was trying the following Linq query;

C#
//define the query
var query = from ce in entities.CompanyExecutives//.Include("Executives").Include("Companies")
    join exec in entities.Executives on ce.ExecutiveID equals exec.ExecutiveID
    join companies in entities.Companies on ce.CompanyID equals companies.CompanyID
        select new
        {
           companies.CompanyName,
           exec.ExecutiveName,
           ce.ExecutiveType,
           ce.DateAppointment,
           ce.DateResignation
        };
Posted

1 solution

Change your LInQ little bit and you will get the result:

C#
var query = from ce in entities.CompanyExecutives//.Include("Executives").Include("Companies")
    join exec in entities.Executives on ce.ExecutiveID equals exec.ExecutiveID
    join companies in entities.Companies on ce.CompanyID equals companies.CompanyID
        select new
        {
           CompanyName = companies.CompanyName,
           ExecutiveName = exec.ExecutiveName,
           ExecutiveType = ce.ExecutiveType,
           DateAppointment = ce.DateAppointment,
           DateResignation = ce.DateResignation
        };
 
Share this answer
 
Comments
pmcm 9-Mar-12 5:08am    
I copied your suggestion into my code but got this error:
"{System.InvalidCastException: Unable to cast object of type 'System.Data.Objects.ObjectQuery`1[<>f__AnonymousType1`5[System.String,System.String,System.Int32,System.Nullable`1[System.DateTime],System.Nullable`1[System.DateTime]]]' to type 'System.Data.Objects.ObjectQuery`1[CompanySecretary.CompanyExecutives]'.
at DAL.DataAccessLayer.GetCompanyExecutivesList()"
pmcm 9-Mar-12 5:11am    
my method now looks like this:
<pre lang="c#">
public ObjectResult<CompanyExecutives> GetCompanyExecutivesList()
{
// Check we have an ObjectContext
if (entities == null) entities = new CompanySecretaryEntities();

//define the query
var query = from ce in entities.CompanyExecutives
join exec in entities.Executives on ce.ExecutiveID equals exec.ExecutiveID
join companies in entities.Companies on ce.CompanyID equals companies.CompanyID
select new
{
CompanyName = companies.CompanyName,
ExecutiveName = exec.ExecutiveName,
ExecutiveType = ce.ExecutiveType,
DateAppointment = ce.DateAppointment,
DateResignation = ce.DateResignation
};


// Create a query from the entityset
ObjectQuery<CompanyExecutives> companyexecutives = (ObjectQuery<CompanyExecutives>)query;

// Return the results
return companyexecutives.Execute(MergeOption.AppendOnly);
}</pre>

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900