Linq to SQL Source in Microsoft ReferenceSource

Question

I google and look in ReferenceSource but I can't find source of LINQ to SQL where LINQ queries converted to SQL statments. Is there any one who knows it?

for example:

from item in Users
select item

Converted to:

SELECT [t0].[UserID], [t0].[Username], [t0].[FirstName], [t0].[LastName], [t0].[IsSuperUser], [t0].[AffiliateId], [t0].[Email], [t0].[DisplayName], [t0].[UpdatePassword]
FROM [Users] AS [t0]

How? I want to see source of this behavior in DotnetFramwork source.

Answer 1

I've used DbCommand off of the datacontext. It takes an IQueryable. System.Data.Linq.DataContext.GetCommand(System.Linq.IQueryable)

Summary: Represents an SQL statement or stored procedure to execute against a data source. Provides a base class for database-specific classes that represent commands.

public void GetSqlCommand()
{
    const string sc2 = @"Server=SQLServerName;Database=DatabaseName;Trusted_Connection=True;";

    using (var dc = new DataContext(sc2))
    {
        var query = dc.GetTable<Users>()
            .Join(dc.GetTable<Phone>(),
                x => x.UserId,
                y => y.LastUserId,
                (x, y) => new { User = x, Phone = y }).Select(x => x);

            DbCommand command = dc.GetCommand(query);

            Assert.IsNotNull(command.CommandText);
    }
}

Then it gives you something like this

SELECT [t0].[UserId], [t0].[Login], [t0].[FullName], [t0].[LastUserId], [t0].[LastDateTime], [t1].[PhoneId], [t1].[PhoneNumber], [t1].[LastUserId] AS [LastUserId2], [t1].[LastDateTime] AS [LastDateTime2]
FROM [dbo].[Users] AS [t0]
INNER JOIN [dbo].[Phone] AS [t1] ON ([t0].[UserId]) = [t1].[LastUserId]

Update you seem to want the code that's used by the framework for building the SQL from the query. Here is a link to the MSFT reference code for the SQLProvider I think you can find your answer in there. SQLProvider

Look at this method it returns the Query Info of the Query expression you passed along. Query Info has the resulting CommandText I mentioned above.

internal QueryInfo[] BuildQuery(Expression query, SqlNodeAnnotations annotations) {.. calls the private BuildQuery..}

private QueryInfo[] BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection<Me.SqlParameter> parentParameters, SqlNodeAnnotations annotations) {...}

......

internal class QueryInfo {
    SqlNode query;
    string commandText;
    ReadOnlyCollection<SqlParameterInfo> parameters;
    ResultShape resultShape;
    Type resultType;

    internal QueryInfo(SqlNode query, string commandText, ReadOnlyCollection<SqlParameterInfo> parameters, ResultShape resultShape, Type resultType) {
        this.query = query;
        this.commandText = commandText;
        this.parameters = parameters;
        this.resultShape = resultShape;
        this.resultType = resultType;
    }
    internal SqlNode Query {
        get { return this.query; }
    }
    internal string CommandText {
        get { return this.commandText; }
    }
    internal ReadOnlyCollection<SqlParameterInfo> Parameters {
        get { return this.parameters; }
    }
    internal ResultShape ResultShape {
        get { return this.resultShape; }
    }
    internal Type ResultType {
        get { return this.resultType; }
    }
}

You'll see the the GetCommand also calls the Build Query Method to generate the the SQL and then get the command text from the first query

DbCommand IProvider.GetCommand(Expression query) 
{
    this.CheckDispose();
    this.CheckInitialized();
    if (query == null) {
        throw Error.ArgumentNull("query");
    }
    this.InitializeProviderMode();
    SqlNodeAnnotations annotations = new SqlNodeAnnotations();
    QueryInfo[] qis = this.BuildQuery(query, annotations);
    QueryInfo qi = qis[qis.Length - 1];
    DbCommand cmd = this.conManager.Connection.CreateCommand();
    cmd.CommandText = qi.CommandText;
    cmd.Transaction = this.conManager.Transaction;
    cmd.CommandTimeout = this.commandTimeout;
    AssignParameters(cmd, qi.Parameters, null, null);
    return cmd;
}   

Answer 2

You can attach an sql profiler using sql server management studio to know the exact query conversion.

Answer 3

I believe the class that looking for is the SqlFormatter class. It will take a tree of SqlNodes and converts it to a database query string using a visitor.

The node tree itself appears to be created by the factory methods in the SqlFactory class.