Asp.net mvc4 how to join entities by using linq

Question

I have a problem with joining two entities by linq language.

I have model Category:

public class Category : DbContext
{
  [Key, Column(Order = 0),DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int CategoryId { get; set; }

  Key, Column(Order = 1)]
  public int ShopId { get; set; }

  public string Name {get;set;} 

  public virtual ICollection<Parameter> Parameter { get; set; }
}

and model Parametr

public class Parameter : DbContext
{
    [Key, Column(Order = 0),DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ParamId { get; set;}

    [Key, Column(Order = 1)]
    public int ShopId { get; set;}

    public string Name {get; set;}
    public string Value {get;set;}
}

The relation is one to many, so one Category coud have 0...n Parameters.

UPDATE: Sure, the relationship is many to many. This is the reason, why Parameter has no CategoryId attribute in the model.

I'm using code first and migration tool for updataing database;

In database MSSQL Express are 3tables. Category, CategoryParameters and Parameters. Table CategoryParameter was created automaticaly and I have no Model for this table.

Creating new Category with multiple Parameters working fine. All 3tables contains valid data.

So my problem now:

I'm trying to load all parameters for one Category. The command looks like:

var parameters = from c in db.Categories
                join p in db.Parameters
                on new { ??? , c.ShopId } equals new { ??? , p.ShopId } 
                where c.ShopId == userProfile.ShopId && c.CategoryId == id
                select new { ParamId = p.ParamId, Name = p.Name };

So my problem is, how to hell join these tables, if there is no usable atribute in the classes.

Many thanks for any help!

MarcinJuraszek · Accepted Answer · 2013-08-12 06:10:14Z

1

You don't need JOIN: use navigation property instead:

Find the category entity.
Use Parameter property of the category entity.

var parameters = db.Categories.First(x => x.CategoryId == 10).Parameter.ToList();

edited Aug 12, 2013 at 6:10

answered Aug 11, 2013 at 22:04

MarcinJuraszek

126k15 gold badges199 silver badges267 bronze badges

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

2 Comments

jkubat Over a year ago

Thx, for your reply. I dont know why, but the error is "Cannot implicity convert bool to int" AND I have to be sure, that Category Belongs to ShopId. This is the reason, why I use ShopId with CategoryId / ParameterId as the composite key in both modesl.

MarcinJuraszek Over a year ago

eh, == instead of =... Updated my answer. I suffer VB.NET <=> C# switching ...

Collectives™ on Stack Overflow

Asp.net mvc4 how to join entities by using linq

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related