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.

You could use Linq-To-Sql. msdn.microsoft.com/en-us/library/bb386961(v=vs.110).aspx — Tim Schmelter
– Tim Schmelter, Commented Aug 13, 2014 at 10:15
-1 What do you mean without Entity Framework or nHibernate or Linq to SQL? You need to use a Linq Provider to generate the SQL! Do you mean you want to only get the SQL and not have EF/nH/L2S RUN the SQL? — Aron
– Aron, Commented Aug 13, 2014 at 10:59
@Aron - what i mean is that the queries will have to be executed out side of entity framework. — SexyMF
– SexyMF, Commented Aug 13, 2014 at 11:08
I have no idea what you just said. You cannot use Linq on its own to create SQL. Linq is agnostic of SQL. Do you mean you do not have an EF model? If so you can consider using database first to create one, and then drop your crm framework. — Aron
– Aron, Commented Aug 13, 2014 at 11:31

Margus · Accepted Answer · 2014-08-13 13:16:36Z

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) :

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)

Collectives™ on Stack Overflow

Converting Linq expression to sql server query

1 Answer 1

Edit

Edit 2

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

Edit 2

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related