CODEWITHSUNDEEP
linq
Alan
Alan

Reputation: 235

Linq distinct problem

I am new to using LINQ, right now i have a query in sql with multiple inner joins and it works fine. I am trying to change this to a equivalent LINQ code, i could able to implement LINQ except for the Distinct fact i am using in my query...

my query

select DT.[Name], count(distinct([FeatureControlID])) as [Value], DT.[Weight]
from [DocumentTypes] DT inner join
[DocumentTypesSchema] DTS on
DT.[ID] = DTS.[DocumentTypeID] inner join
ProductsFeaturesControlsDocumentValues [PFCDV] on
DTS.[ID] = PFCDV.[SchemaID]
where [PFCDV].[ProductID] = 72
group by DT.[Name], DT.[Weight], [DT].[ID]
order by [DT].[ID]

and my LINQ without the Distinct condition is as below

 from dt in db.DocumentTypes
join dts in db.DocumentTypesSchemas on new { ID = dt.ID } equals new { ID = dts.DocumentTypeID }
join pfcdv in db.ProductsFeaturesControlsDocumentValues on new { ID = dts.ID } equals new { ID = pfcdv.SchemaID }
where
  pfcdv.ProductID == 72
group new {dt, pfcdv} by new {
  dt.Name,
  dt.Weight,
  dt.ID
} into g
orderby
  g.Key.ID
select new  {
  g.Key.Name,
  Value = (Int64?)g.Count(p => p.pfcdv.FeatureControlID != null),
  Weight = (System.Decimal?)g.Key.Weight
}

can anyone give me a hand on this? actually the linq code executes without the Distinct feature i used in the code.

Upvotes: 0

Views: 451

Answers (1)

dahlbyk
dahlbyk

Reputation: 77500

Have you tried something like this?

...
select new  {
  g.Key.Name,
  Value = (Int64?)g.Select(p => p.pfcdv.FeatureControlID)
                   .Where(id => id != null)
                   .Distinct().Count()
  Weight = (System.Decimal?)g.Key.Weight
}

Upvotes: 1

Related Questions