Ed Tijgen

Hi All,
Please consider the following database model


Boy (table name)
pk_BoyId (field)
Name (field)

BoyGirl (table name)
fk_BoyId (field)
fk_GirlId (field)

Girl (table name)
pk_Girl (field)
Name (field)

When using the ADO.NET Entity Framework model wizard on this database model the result is 2 entities

Boy (Entity)
pk_BoyId (Member)
Name (Member)
Girl (navigation property)

Girl (Entity)
pk_GirlId (Member)
Name (Member)
Boy (navigation property)


Now how can I create a collection of girls to use in a join on Boy
So I'd like to get every Boy that has a relation to a girl from the collection of girls I created seperatly from the database.
Something like this:

List<Girl> girls = new List<Girl>(); // please note that Girl is infact from the entity model in the context
Girl girl = new Girl();
girl.GirlId = 1;
girl.Name = "Paula";
girls.Add(girl);
girl = new Girl();
girl.GirlId = 3;
girl.Name = "Debby";
girls.Add(girl);

var boys = from b in context.Boy
//where b.Girl in girls (what ever I try I cant get this to work)

select b;
foreach (Boy resultBoy in boys)
{
Console.WriteLine(resultBoy.Name);
}

anyone have an idea

Regards,

Ed




Re: ADO.NET (Pre-release) Simple LINQ join

BACHRATY Gergely

Try this:

Code Block

var boys = from b in context.Boy

where girls.Contains(b.Girl)

select b;





Re: ADO.NET (Pre-release) Simple LINQ join

Ed Tijgen

var boys = from b in context.Boy
where girls.Contains(b.Girl)
select b;

results in:

cannot convert from 'System.Data.Objects.DataClasses.EntityCollection<WhoDunItModel.Girl>' to 'WhoDunItModel.Girl'

var boys = from b in context.Boy
where b.Girl.Contains(girls) //(the other way around)
select b;

results in:

System.Collections.Generic.List<WhoDunItModel.Girl>' to 'WhoDunItModel.Girl'

The last one is not working becuase the contains on the b.Girl expects another Girl object as paramenter so lets ignore that further.

The first one however: The contains method on the girls colection expects a Girl object as parameter which it gets. But then why the error message

Anyone an idea





Re: ADO.NET (Pre-release) Simple LINQ join

BACHRATY Gergely

Oh sorry, missed the n:m mapping.

Next try is:

Code Block

var boys = from b in context.Boy
where b.Girl.Any(g=>girls.Contains(g))
select b;

Though I'm not exactly sure if this is the most effective solution.
The first try is failing before both the girls and b.Girl is a collection. The former is a List<Girl>, the latter is an EntityCollection<Girl>.




Re: ADO.NET (Pre-release) Simple LINQ join

Kati Iceva - MSFT

Is seems like there is a many-to-many relationship between Boy and Girl, thus the navigation properties Boy.Girl and Girl.Boy are of a collection type. That causes the exception message for "girls.Contains(b.Girl)", b.Girl is really a collection of Girls.

You can try this:

Code Block

var query = (

from girl in context.Girl //or some filtered subset

from boy in girl.Boy

select boy

).Distinct();

HTH,

Kati






Re: ADO.NET (Pre-release) Simple LINQ join

Ed Tijgen

var boys = from b in context.Boy
where b.Girl.Any(g=>girls.Contains(g))
select b;

results in this error:
The 'Boolean Contains(WhoDunItModel.Girl)' method is not recognized by LINQ to Entities, and cannot be translated into a store expression.

and

var query = (

from girl in context.Girl //or some filtered subset

from boy in girl.Boy

select boy

).Distinct();

resulted in this error:

The specified value is not an instance of a valid constant type
Parameter name: value

I am getting nervous as my project depends on it. I would think this kind of thing is at the very basic level of operations of LINQ or perhaps there is some intricate thing that eludes me and that I am unable to depict in my question

anyone have an idea

Regards,

Ed





Re: ADO.NET (Pre-release) Simple LINQ join

Kati Iceva - MSFT

Hi Ed,

I followed the steps you described in your original post for creating the model, then run the query I gave you in my earlier post and was not able to reproduce the error you reported ( "The specified value is not an instance of a valid constant type. Parameter name: value".) This was over the beta 2 bits, which I assume you are using.

Are doing anything else before executing that query that may cause the error

I can further investigate if you would like to provide further context (the whole program you are executing along with the edmx file). If you prefer you can directly email it to me at kati dot iceva at microsoft dot com.

Thanks,

Kati