А вот со вторым EXISTS (точнее с генерацией вложенного SELECT) действительно не всё так просто. Надо подумать, может как-то можно решить и эту проблему.

SELECT
  X.*
FROM
  X
INNER JOIN
  Y ON Y.ID = X.ID
WHERE
  Y.Field = @Value

DECLARE @PreFilteredY TABLE
(
  ID INT
);

INSERT INTO
  @PreFilteredY
SELECT
  ID
FROM
  Y
WHERE
  Y.Field = @Value

SELECT
  X.*
FROM
  X
INNER JOIN
  @PreFilteredY AS Y ON Y.ID = X.ID

A>DECLARE @PreFilteredY TABLE
A>(
A>  ID INT
A>);

A>INSERT INTO
A>  @PreFilteredY
A>SELECT
A>  ID
A>FROM
A>  Y
A>WHERE
A>  Y.Field = @Value

A>SELECT
A>  X.*
A>FROM
A>  X
A>INNER JOIN
A>  @PreFilteredY AS Y ON Y.ID = X.ID

class MyDbManager : DbManager
{
    public Table<T> CreateTable<T>()
        where T : class
    {
        return CreateTable<T>(null);
    }

    public Table<T> CreateTable<T>(string tableName)
        where T : class
    {
        var table = new SqlTable<T>();
        var sb    = new StringBuilder();

        sb.AppendFormat("CREATE TABLE {0}\n(", tableName ?? table.Name);

        foreach (var field in table.Fields)
        {
            var f      = field.Value;
            var type   = SqlDataType.GetDataType(f.SystemType);
            var dbType = type.SqlDbType;
            var len    = type.Length > 0 ? "({0})".Args(type.Length) : type.Precision > 0 ? "({0},{1})".Args(type.Precision, type.Scale) : "";

            switch (dbType)
            {
                case SqlDbType.UniqueIdentifier :
                    dbType = SqlDbType.VarChar;
                    len    = "(36)";
                    break;
            }

            sb.AppendFormat("\n\t{0}\t{1}{2}\t{3}NULL{4},",
                f.PhysicalName,
                dbType,
                len,
                f.Nullable ? "" : "NOT ",
                f.IsIdentity ? "\t IDENTITY" : "");
        }

        sb.Length -= 1;
        sb.Append("\n)");

        SetCommand(sb.ToString());
        ExecuteNonQuery();

        var t = GetTable<T>();

        if (tableName != null)
            t = t.TableName(tableName);

        return t;
    }

    public void TruncateTable<T>()
        where T : class
    {
        var table = new SqlTable<T>();
        var sb    = new StringBuilder();

        sb.Append("TRUNCATE TABLE ");

        DataProvider.CreateSqlProvider().BuildTableName(sb, table.Database, table.Owner, table.Name);

        SetCommand(sb.ToString()).ExecuteNonQuery();
    }

    public void TruncateTable<T>(string dbName, string table)
        where T : class
    {
        var sb = new StringBuilder();

        sb.Append("TRUNCATE TABLE ");

        DataProvider.CreateSqlProvider().BuildTableName(sb, dbName, null, table);

        SetCommand(sb.ToString()).ExecuteNonQuery();
    }
}

[TableName("@PreFilteredY")
class PreFilteredY
{
    public int ID;
}

using (var db = new MyDbManager()
{
    var preFiltered = db.CreateTable<PreFilteredY>();

    db.GetTable<Y>
        .Where(y => y.Field = @Value)
        .Insert(preFiltered, y => new PreFilteredY { ID = y.ID });

    var q =
        from x in db.GetTable<X>()
        join p in preFiltered on x.ID equals p.ID
        select x;

    ...
}

CREATE FUNCTION [GetCustomers](
    @Timestamp DATETIME2)
RETURN TABLE
WITH SCHEMABINDING
AS
RETURN
(
    ...
)

SELECT
    *
FROM
    [GetCustomers](@Timestamp)
WHERE
    [LastName] LIKE '%кач%'

A>SELECT
A>    *
A>FROM
A>    [GetCustomers](@Timestamp)
A>WHERE
A>    [LastName] LIKE '%кач%'

var q =
    from c in GetCustomers(timestamp)
    where c.LastName.Contains("кач")
    select c;

from p in db.Product
join c in db.Category on p.CategoryID equals c.CategoryID into g
from c in g.DefaultIfEmpty()
select new
{
    c.CategoryName,
    p.ProductName
};

from p in db.Product
select new
{
    p.Category.CategoryName,
    p.ProductName
};

SELECT Bbb.AaaId FROM Aaa INNER JOIN Bbb ON Aaa.A001 = Bbb.B001
SELECT Bbb.AaaId FROM Aaa LEFT JOIN Bbb ON Aaa.A001 = Bbb.B001

from aaa in Aaa
join bbb in Bbb
    on aaa.A001 equals bbb.B001 /*здесь aaa.A001 еще not nullable*/
right join ccc in Ccc
    on aaa.A001 equals ccc.C001 /*здесь aaa.A001 еще not nullable*/
join ddd in Ddd
    on aaa.A001 equals ddd.D001 /*здесь aaa.A001 уже nullable*/
select
    aaa.A002 /*nullable из-за второго right join-а*/
    ссс.С002 /*not nullable*/

from t in
    db.Table.Select(t => new Table { Field1 = t.Field1 }).Union(
    db.Table.Select(t => new Table { Field2 = t.Field2 }));
where ...

class Table
{
    public int Field1;
    public int Field2;
}

SELECT Field1, NULL FROM Table1
UNION
SELECT NULL, Field2 FROM Table1

from aaa in Aaa
left join bbb in Bbb
on aaa.A001 equals bbb.B001
select new
{
    // если тебе нужен весь объект, то он будет null
    bbb,
    // если отдельное поле
    B001 = bbb == null ? (int?)null : bbb.B001,
    B002 = bbb == null ? (int?)null : bbb.B002
};

class A
{
    public int Field1;
    public int Field2;
}

new A { Field1 = 1 };

CREATE TABLE A
(
  [ID] INT IDENTITY PRIMARY KEY NOT NULL,
  [Length] INT NOT NULL
)

CREATE TABLE B
(
  [ID] INT IDENTITY PRIMARY KEY NOT NULL,
  [Weight] INT NOT NULL
)

SELECT
  [A].[Length],
  [B].[Weight]
FROM
  [A]
RIGHT JOIN
  [B] ON [B].[ID] = [A].[ID]

SELECT
  [A].[Length],
  ISNULL([B].[Weight], 0) AS [Weight]
FROM
  [A]
RIGHT JOIN
  [B] ON [B].[ID] = [A].[ID]

SELECT
  [A].[Length],
  [B].[Weight]
FROM
  [A]
RIGHT JOIN
  [B] ON [B].[ID] = [A].[ID]
WHERE
  [B].[Weight] IS NOT NULL

db.Table.Select(t => new { Field1 = (int?)t.Field1, Field2 = (int?)null }).Union(
db.Table.Select(t => new { Field1 = (int?)null, Field2 = (int?)t.Field2 }))
.Select(t => new Table { Field1 = t.Field1, Field2 = t.Field2 })

IT>from aaa in Aaa
IT>left join bbb in Bbb
IT>on aaa.A001 equals bbb.B001
IT>select new
IT>{
IT>    // если тебе нужен весь объект, то он будет null
IT>    bbb,
IT>    // если отдельное поле
IT>    B001 = bbb == null ? (int?)null : bbb.B001,
IT>    B002 = bbb == null ? (int?)null : bbb.B002
IT>};
IT>

from aaa in Aaa
left join bbb in Bbb
on aaa.A001 equals bbb.B001
select new
{
    bbb.B001,
    bbb.B002
};

AP>db.Table.Select(t => new { Field1 = (int?)t.Field1, Field2 = (int?)null }).Union(
AP>db.Table.Select(t => new { Field1 = (int?)null, Field2 = (int?)t.Field2 }))
AP>.Select(t => new Table { Field1 = t.Field1, Field2 = t.Field2 })
AP>

AP>from aaa in Aaa
AP>left join bbb in Bbb
AP>on aaa.A001 equals bbb.B001
AP>select new
AP>{
AP>    bbb.B001,
AP>    bbb.B002
AP>};
AP>

string s = null;
s.Substring(1);

На самом деле это более общая проблема
http://rsdn.ru/forum/design/4217251.1.aspx

Автор: IT
Дата: 31.03.11

...
left join t ...
select t.Aaa

string s = null;
s.Substring(1);