LINQ to SQL : What is DataContext.Translate Method

  • Article

DataContext’s Translate method converts existing IDataReader to an IEnumerable<T>. This is very useful when you may need to run raw SQL Query though System.Data.Linq. I am getting few similarities with DataContext’s ExecuteQuery.

 

How Translate works

+++++++++++++

Assuming I am using Northwind database.

 

 

using (NWDataContext db = new NWDataContext())

{

    DbConnection conn = db.Connection;

    conn.Open();

    DbCommand comm = conn.CreateCommand();

    comm.CommandText = "SELECT * FROM Customers";

    DbDataReader reader = comm.ExecuteReader();

    var q = db.Translate<Customer>(reader);

    ObjectDumper.Write(q);

}

 

Now since my dbml has Customer class defined and I am running SELECT * query this can map with existing Customer class. But if I want to retrieve only the selected columns from the result set then one of the drawbacks is that I cannot project the output using Anonymous Type, rather I need to define a class in my app to project it to IEnumerable<T>. One of the reasons is that the Anonymous Type does not create any default public constructor. It would have been nice to have this anonymous type projection with the Translate.

 

So if you want to map it to your type then your approach would be little different.

 

using (NWDataContext db = new NWDataContext())

{

    DbConnection conn = db.Connection;

    conn.Open();

    DbCommand comm = conn.CreateCommand();

    comm.CommandText = "SELECT CustomerId, CompanyName FROM Customers";

    DbDataReader reader = comm.ExecuteReader();

    var q = db.Translate<CustomerSelect>(reader);

    ObjectDumper.Write(q);

}

 

Extra things you need is that,

 

class CustomerSelect

{

    public int CustID { get; set; }

    public string CompanyName { get; set; }

}

 

Whereas in ExecuteQuery method you do things little differently,

 

using (NWDataContext db = new NWDataContext())

{

    var q = db.ExecuteQuery<Customer>("SELECT * FROM Customers", "");

    ObjectDumper.Write(q);

}

 

 

Namoskar!!!