Linq query help on EDM bound DataGridView

Question

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 8-Mar-12 22:53pm

pmcm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ganesan Senthilvel · Accepted Answer · 2012-03-08T22:58:00

Solution 1

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

Posted 8-Mar-12 22:58pm

Ganesan Senthilvel

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>