LinQ query - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

LinQ query

jake5958jake5958 Posts: 3Member
[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

  • jake5958jake5958 Posts: 3Member
    Got it resolved. I came up with the following linQ query:

    [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]
Sign In or Register to comment.