locked
Subselect because of XML data type issues. RRS feed

  • Question

  • Hello,
     
    i have following scenario:

    Table1 (t1):
    t1.id
    t1.name
    t1.xmldata (xml datatype)

    Table2 (t2):
    t2.id
    t2.t1id (foreign key to t1)
    t2.expression

    I want to select t1 filtered by t2.expression, and only want to return table t1.

    example:

    var query =
    (from t1 in dbContext.Table1
    from t2 in t1.Table2s
    where t2.Expression == "test"
    select t1).Distinct;

    This query throws (the expected) exception: The xml data type cannot be selected as DISTINCT because it is not comparable.

    In SQL I can use the following statements to archive the expected result:

    select t1.* from t1
    where t1.Id in
    (
     Select t1.Id
     from t1
     inner join t2 on t1.Id = t2.t1id
     where
     t2.expression = 'Process:2' or t2.expression = 'Process:2'
    )

    select t1.*
    from
     t1 inner join
     (
      Select t1.Id
      from t1
      inner join t2 on t1.Id = t2.t1id
      where
      t2.expression = 'Process:2' or t2.expression = 'Process:2'
     ) filtered on t1.Id = filtered.Id



    How to you define such a query in LINQ?
    Saturday, February 7, 2009 10:43 AM

Answers