How can I directly execute SQL queries in linq

34,156

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();
}
Share:
34,156

Related videos on Youtube

shamim
Author by

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, 2020

Comments

  • shamim
    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
      zerkms almost 13 years
      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?
  • Matt
    Matt over 7 years
    Correct, but the question was about Linq. You cannot use the results directly in Linq.
  • Matt
    Matt over 7 years
    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>.
  • Mark Cidade
    Mark Cidade over 7 years
    @Matt: The question was about the LINQ-to-SQL API, not the language query feature.
  • Matt
    Matt over 7 years
    Ok, I agree. However, I prefer the Generic notation.
  • chri3g91
    chri3g91 about 2 years
    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();

Related