Clash Royale CLAN TAG#URR8PPP

how to get a table data where should be checked in another table without joining using linq include or another way?

i want to get the data of tables where is included in another table but is filtered by a column id that has another realtion with another table

in below i wrote the details

products table(only needable columns written)

public partial class Products

public int ProductID get; set;
public string ProductName get; set;
public int GroupID get; set;

public virtual ICollection<Product_Attribut> Product_Attribut get; set;



Attribute_Item table

public partial class AttributItem

public int AttributeID get; set;
public int AttrGrpID get; set;
public string Name get; set;


public virtual ICollection<Product_Attribut> Product_Attribut get;set;



Product_Attribut table

public partial class Product_Attribut

public int ID get; set;
public Nullable<int> ProductID get; set;
public Nullable<int> AttributeID get; set;
public Nullable<bool> isChecked get; set;
public string Value get; set;

public virtual AttributItem AttributItem get; set;
public virtual Products Products get; set;



tables are products , Attribute_Item , Product_Attribut that Product_Attribut has 1:m relation with these two table which means two 1:m relation

now i want to get products data table where in Product_Atrribute there should be a row with productID and AttributeID where AttributeID is filtered by a filter ID

products = products.Include(pr => pr.Product_Attribut.Select(x => x.AttributeID == Convert.ToInt32(eachfilt[1])));


I used this code but i know that is wrong and get the error but don't know how to reach it ? do i need joining two tables or theres another simple way just using Linq?

sample

product rows
product_id
1
2
3
4
5

AttributItem rows
attribute_id
11
12
13


now in Product_Attribut tables have this rows

product_id attribute_id
1 11
2 11
2 12
3 12


when i set the attribute_id filter to 11 i want the output be the product table with just product 1 and 2 not (3,4 and 5)

2 Answers
2

this is the way you get the data of a table and make filter on another table with one to many relation to this table and the result and output is just the product table and data as what you want to get

var products = db.Products.Include(p => p.Product_Groups);

products = products.Include(p => p.Product_Attribut).Where(x => x.Product_Attribut.Any(p => p.AttributeID == filter));


You can check below code to get this thing.
You have one to many relationship so from Product_Attribut property we can filter data with the help of linq where clause and then select what user want using annonymous function or the class present in your example.

Products product = new Products();
Product_Attribut attribute = new Product_Attribut();
attribute.ProductID = 1;
attribute.AttributeID = 11;


Product_Attribut attribute1 = new Product_Attribut();
attribute1.ProductID = 2;
attribute1.AttributeID = 11;

Product_Attribut attribute2 = new Product_Attribut();
attribute2.ProductID = 2;
attribute2.AttributeID = 12;

Product_Attribut attribute3 = new Product_Attribut();
attribute3.ProductID = 3;
attribute3.AttributeID = 12;

Product_Attribut attribute4 = new Product_Attribut();
attribute4.ProductID = 4;
attribute4.AttributeID = 14;

product.Product_Attribut = new List<Product_Attribut>();
product.Product_Attribut.Add(attribute);
product.Product_Attribut.Add(attribute1);
product.Product_Attribut.Add(attribute2);
product.Product_Attribut.Add(attribute3);
product.Product_Attribut.Add(attribute4);

var filter = product.Product_Attribut.Where(x => x.AttributeID == 11).Select(x => new Product_Attribut

ProductID = x.ProductID,
AttributeID = x.AttributeID
).ToList();


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.