How can I directly execute SQL queries in linq
Solution 1
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
Solution 2
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();
}
Related videos on Youtube
shamim
Working on Microsoft .NET Technology with over 8 years of industry experience. Responsible for developing and managing several applications build on .NET technology. In my day to day, work needs to maintain a set of well-defined engineering practices developed by me and as well as other developer communities, having knowledge in domain-driven design, design architectural framework, user experience, usability engineering and project management.
Updated on February 17, 2020Comments
-
shamim about 4 years
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.
-
zerkms almost 13 yearsHow 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?
-
-
Matt over 7 yearsCorrect, but the question was about Linq. You cannot use the results directly in Linq.
-
Matt over 7 yearsNote: 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 valuenull
to avoid warnings and use result typeIEnumerable<JobsDto>
. -
Mark Cidade over 7 years@Matt: The question was about the LINQ-to-SQL API, not the language query feature.
-
Matt over 7 yearsOk, I agree. However, I prefer the Generic notation.
-
chri3g91 about 2 yearsIn 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();