Filter values based on custom attribute in another table

You may try this in SQL

select DA.Id, DA.Product, CA.CustomeAttributeValue as IsOrganic  from DeafaultAtrributeTable as DA inner join CustomAttributeTable as CAon DA.Id = CA.DefaultAttributeMappingId

In LINQ

var query =   from DA in DeafaultAtrributeTable    join CA in CustomAttributeTable  on DA.ID equals CA.DefaultAttributeMappingId   where CA.CustomeAttributeValue == true   select new { Id = DA.Id, Product = DA.Product, IsOrganic = CA.CustomeAttributeValue };

or LINQ extension method is

  var query = DeafaultAtrributeTable     // your starting point - table in the "from" statement      .Join(CustomAttributeTable  , // the source table of the inner join         DA => DA.ID,        // Select the primary key (the first part of the "on" clause in an sql "join" statement)         CA => CA.DefaultAttributeMappingId,   // Select the foreign key (the second part of the "on" clause)        (DA, CA) => new { Id = DA.Id, Product = DA.Product, IsOrganic =      CA.CustomeAttributeValue }) // selection     .Where(RES => RES.CA.CustomeAttributeValue == true);    // where statement

try this

public class Filtered{    public int ProductId { get; set; }    public string ProductName { get; set; }    public bool? IsOrganic { get; set; }    public bool? IsDisposable { get; set; }}var result = defaultAttributeTable.Join(customAtrributeTable, DAT => DAT.Id, CAT => CAT.DefaultAttributeTableId, (DAT, CAT) =>             new Filtered             {//your join with null values for opposing isdisposable and isorganic                ProductId = DAT.Id,                IsDisposable = CAT.CustomAtrributeName == "IsDisposable" ? (bool?)CAT.CustomeAttributeValue : null,                IsOrganic = CAT.CustomAtrributeName == "IsOrganic" ? (bool?)CAT.CustomeAttributeValue : null,                ProductName = DAT.Product            }).GroupBy(q => q.ProductId) //group it by productid              .Select(q =>                   new Filtered                   {//this will flatten opposing isorganic and isdisposable                       ProductId = q.Key,                       IsDisposable = q.First(e => e.IsDisposable.HasValue).IsDisposable.Value,                       IsOrganic = q.First(e => e.IsOrganic.HasValue).IsOrganic.Value,                       ProductName = q.First(e => e.ProductId == q.Key).ProductName                   }).ToList();