[code]Don't know if this is the correct forum, my apologies if not.
[b]
I'm new to LinQ and I'm having trouble converting the following TSQL query to a LinQ query:[/b]
[color=Blue][size=3] SELECT r.*
FROM [Rule] r
INNER JOIN [RuleType] rt
ON r.[RuleTypeId] = rt.[RuleTypeId]
AND rt.[RuleCategoryId] = 1
AND 35 IN (SELECT DISTINCT c.StateId
FROM [Citation] c
WHERE r.[CitationId] = c.[CitationId]
UNION
SELECT DISTINCT a.StateId
FROM [Agency] a
WHERE r.[AgencyId] = a.[AgencyId])[/size][/color]
[b]The DB looks like:[/b]
[size=1]--------------- ------------------
| Rule | | RuleType | ------------------
--------------- -----------------| | RuleCategory |
| RuleId | -----+| RuleTypeId | ------------------
| RuleName | | | RuleCategoryId |>o-------------------+| RuleCategoryId |
| RuleDesc | | | RuleType | | RuleCategory |
| RuleTypeId |>o--| -----------------| -------------- ------------------
| CitiationId |>o------- | Citation |
| AgencyId |>o-- | -------------|
--------------- | |---------------------+| CitationId |
| | Citation |
| | StateID |
| -------------- --------------
| | Agencey | V
| -------------- o
------+| AgencyId | |
| AgencyName | |
| AgencyDesc | |
| StateId |>o-----------|
-------------- |
|
+
-------------
| State |
-------------
| StateId |
| StateName |
-------------[/size][color=Green][/color][/code][code][code][/code][/code]
Comments
[code][color=Blue][size=3]
from r in context.Rules
join rt in context.RuleTypes on r.RuleTypeId equals rt.RuleTypeId
where rt.RuleCategoryId == ruleCategoryId
&& (from c in context.Citations
where c.StateId == stateId
select c.CitationId).Contains(r.CitationId)
&& (from a in context.Agencies
where a.StateId == stateId
select a.AgencyId).Contains(r.AgencyId)
select r[/size][/color][/code]