How can I directly execute SQL queries in linq

Question

In C# with VS 2008,I have a query ,In this query i join more than one tables,so i don't know the type , I want to know how to directly run a sql query in linq .

IEnumerable<Type> results = db.ExecuteQuery<TYpe>("sql query")

My above query works fine but I want to avoid type, I want to write

var results = db.ExecuteQuery("sql query");

Is there any way to write it?

Thanks in advance.

How is it supposed for linq2sql to detect what class it needs to map the results? Btw, you're still able to use var in the first sample, aren't you? — zerkms
– zerkms, Commented Apr 12, 2011 at 5:00
Is it possible to use a stored procedure? The designer will generate a class for each stored procedures return type if so. — Luke Lowrey
– Luke Lowrey, Commented Apr 12, 2011 at 6:13
Give some sample queries. What are you trying to accomplish? Are the return types the same, but just from different tables? — jaraics
– jaraics, Commented Apr 12, 2011 at 6:49
You can't run SQL in LINQ. LINQ is a C# language concept. It doesn't run SQL. That said, many ORMs offer options to run raw SQL. How to do that generally isn't hard to find in their documentation. — Gert Arnold
– Gert Arnold, Commented Feb 16, 2020 at 14:40

HB MAAM · Accepted Answer · 2020-02-16 13:23:39Z

12

    var result = dataContext.ExecuteQuery<JobsDto>
                 ("Select JobID,JobName From jobs");

but make sure JobsDto has two properties JobID and JobName and there types the same type as the table columns

PS. DTO stand for Data Transfer Object

edited Feb 16, 2020 at 13:23

answered Feb 6, 2013 at 6:46

HB MAAM

4,0124 gold badges33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Over a year ago

Note: If you just need a quick test, you can also make the properties dynamic like so: public class JobsDto { public dynamic JobsID; public dynamic JobName; }, and use a tool like LinqPad to dump the result. Assign them the default value null to avoid warnings and use result type IEnumerable<JobsDto>.

chri3g91 Over a year ago

In my case, I needed to select aggregated values. Create a new class model "dto", as suggested. But had to alter a bit. dbContext.Database.SqlQuery<AggregatedValuesDto>("SQL statement").SingleOrDefault();

Mark Cidade · Accepted Answer · 2011-04-12 05:19:20Z

5

You need to specify the type to map to from the query results. You can use a System.Type object instead of statically specifying it as a generic type parameter:

var results = db.ExecuteQuery(typeof(Customer), "sql query ");

If you just want a plain ADO.NET DataReader you could use the DataContext.Connection property:

using (var cmd = db.Connection.CreateCommand()) 
{ 
  cmd.CommandText = "sql query ";
  var results = cmd.ExecuteReader();
}

edited Apr 12, 2011 at 5:19

answered Apr 12, 2011 at 5:11

Mark Cidade

100k33 gold badges231 silver badges238 bronze badges

3 Comments

Matt Over a year ago

Correct, but the question was about Linq. You cannot use the results directly in Linq.

Mark Cidade Over a year ago

@Matt: The question was about the LINQ-to-SQL API, not the language query feature.

Matt Over a year ago

Ok, I agree. However, I prefer the Generic notation.

Collectives™ on Stack Overflow

How can I directly execute SQL queries in linq

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related