Linq to Entity Framework m2m query with simple string constraint

Question

Trying to learn Linq to EF in MVC3 project...
Many to many relationship in Entity Framework:

        Shirt              ShirtType           Type
     -----------        -------------        ---------
       ShirtID             ShirtID            TypeID
      ShirtName            TypeID            TypeName
      ShirtPrice

No other fields in ShirtType maping table so now I've got navigation properties in both entities Shirt.Type and Type.Shirt.
Before generating entities from database i've created complex PK in ShirtType including both foreign keys.
I've populated the database with values.
Here's the code which i used to query the database in order to return shirts that are referenced with type Type.Name==tName

public ActionResult Browse(string tName)
        {
            using (TShopEntities db=new TShopEntities())
            {
                var typeModel = from s in db.Shirt
                                from t in s.Type
                                where t.TypeName == tName
                                select new tsStore.ModelView.ShirtView()
                                {
                                    ShirtName=s.ShirtName,
                                    ShirtPrice=s.ShirtPrice
                                };
                return View(typeModel.ToList());
            }
        }

My linq query returns nothing and the view prints just headers. I'm stuck here for a while now, is the linq query wrong or should i look for some other error?

RPM1984 · Accepted Answer · 2011-08-16 00:44:32Z

1

Assuming this is the query you want:

return shirts that are referenced with type Type.Name==tName

And assuming your EF model and database is correct (hard to tell, since i can't see a screenshot of your EDMX or your FK's).

You should have 2 entities on your EDMX - Shirt and Type. The join table will be hidden as EF is smart enough to do an implicit join behind the scenes.

Then this query should work:

var shirtsOfAParticularType = db.Shirts.Where(shirt => shirt.Types.Any(type => type.TypeName == "someTypeName")).ToList();

Remember, since it's a many..many, a shirt can have many types. So the query above will return shirts that have at least one type of the one you specify.

Is that what you want?

answered Aug 16, 2011 at 0:44

RPM1984

73.3k62 gold badges241 silver badges364 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

l0lander Over a year ago

Thx man, i actually figured out that both queries are fine, but the problem is something else. When i tried your query i've got the same empty list, and then i just hardcoded some value in string "someTypeName" xmpl "Bands" and it worked. So the problem is in URL resolution from navigation properties. Will update you with actual problem, thx for reflection, it's strange how and when brain starts working :)

RPM1984 Over a year ago

@streetspirit - not sure what you mean by "the problem is in URL resolution from navigation properties". Will wait for your edit.

l0lander Over a year ago

Something's wrong with route parameters, somehow Action method doesn't get value in string parameter tName which is supposed to get from localhost:1491/Home/Browse?type=Bands when link selected... working on it

l0lander Over a year ago

Ohh my... what an idiot, i didn't notice that the name of the string parameter that i called tName must be the same like query variable in url that i named "type"... Do you get what was the problem now, can't believe it

RPM1984 Over a year ago

yep, i get you - route token and param name mismatch. On a side node, you should include a route for that, so the Url is like /Home/Browse/Bands. Must much RESTful. Unless this is a test/fun project, in which case dont worry

|

Naor · Accepted Answer · 2011-08-16 00:43:24Z

When you load all the three entities to the edmx, the entity framework created two entities and not three:

A. Shirt - with scalar properties of ShirtID, ShirtName and ShirtPrice.   
           with navigation property to many Types (IEnumerable<Type>).   
B. Type - with scalar properties of TypeID and TypeName.   
          with navigation property to many Shirts (IEnumerable<Shirt>).

Then, after creating the code for your entities, you can do:

public ActionResult Browse(string tName)
        {
            using (TShopEntities db=new TShopEntities())
            {
                var typeModel = from s in db.Type
                                select Shirts;
                return View(typeModel.ToList());
            }
        }

And thats all. Don't consist creating another entity for ShirtType because this is not the right way of creating your model.

Collectives™ on Stack Overflow

Linq to Entity Framework m2m query with simple string constraint

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related