Converting Linq expression to sql server query

Question

I am using some crm framework and this framework does not have any internal orm and is not using entity framework, only plain sql queries.

I have Entity for each table in the database. So I have for example:

public class Customer{
    public string FirstName{get;set;}
    public int Status{get;set;}
}  

Is there anyway I can write linq queries and convert them into sql without using entity framework or NHibernate? I am looking for something like.

IQueryable linq = from LinqProvider.Get<Customer>() int customer where customer.FirstName == "test" and Status > 1;

string sqlQuery = LinqProvider.ToSqlQuery(linq);

//Select * from Customer where FirstName = "test" and Status > 1

I would live to have some advanced functionality like Join sort and aggregation functionality.

Answer 1

Note difference between following 2 lines (linq in lambda form) :

var dataQ = Customer.Where(o=>(o.FirstName == "test" && o.Status > 1);
var dataL = Customer.Where(o=>(o.FirstName == "test" && o.Status > 1).ToList();
var dataS = Customer.SingleOrDefault(o=>(o.FirstName == "test" && o.Status > 1);

As far as I am aware linq queries are converted to lamba and then optimized and auto-compiled (from framework 4.5). By default your database context should have lazy loading and optimistic concurrency turned on. Lazy loading means data is not fetched before you actually need it. In this case .ToList() and SingleOrDefault will force data to be retried. That means they will show up in Entity Framework Profiler.

If you do not want to use it or can not, then you can use ´ToTraceString´, not however it will not work on dataL or dataS because they are not queries, but concrete instances.

File.AppendAllText(traceFile, ((ObjectQuery)dataQ).ToTraceString());   
return dataQ.ToList();

Edit

Assumptions that I made:

• My assumption is that you can not write proper SQL, but are somewhat familiar with Linq.
• You have uncommon database that would use 3rd party provider or you can not even do that.
• You (manually?) created mapping for database tables

Now what you can do is use code first approach. You generate database from those classes. Then you query it and you get your SQL. I assumed this is clear. Note that you might want to get code-first-migrations as well, because you most likely need to make changes.

Examples (just google) :

• http://msdn.microsoft.com/en-us/data/jj193542.aspx
• http://msdn.microsoft.com/en-us/data/jj591621.aspx

Edit 2

Made an example: https://gist.github.com/margusmartsepp/f9fcc9178600ca53acf6

    [Table("CustomerTest")]
    public class Customer
    {
        [Key]
        public int Id { get; set; }
        public string FirstName { get; set; }
        public int Status { get; set; }
    }

    public class CustomerContext : DbContext
    {
        public CustomerContext(): base("name=Program.CustomerContext"){}
        public DbSet<Customer> Customers { get; set; }
    }
    //PM> Install-Package EntityFramework
    //PM> Install-Package EntityFramework.SqlServerCompact
    static void Main(string[] args)
    {
        using (var db = new CustomerContext())
        {
            var item = new Customer {FirstName = "test", Status = 2};
            db.Customers.Add(item);
            db.SaveChanges();

            var items = db.Customers.Where(o => (o.FirstName == "test" && o.Status > 1));
            Console.WriteLine(items.ToString());
        }
        Console.ReadKey();
    }

Example output:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[FirstName] AS [FirstName],
    [Extent1].[Status] AS [Status]
    FROM [CustomerTest] AS [Extent1]
    WHERE (N'test' = [Extent1].[FirstName]) AND ([Extent1].[Status] > 1)