To String Conversion while doing Join in LINQ

Question

I am using LINQ query to fetch the data from database and also doing joins in the LINQ query as,

(
from accountTransaction in AccountTransactions
join xlkpQualifier in Enumerations on 
    accountTransaction.LkpQualifier.ToString() equals xlkpQualifier.Value 
select top accountTransaction)
.Take(10);

I am getting Exception on accountTransaction.LkpQualifier.ToString() that System.ToString() can't be used in LINQ Entities.

But I am getting problem here in conversion to string on Join. How can I do it?

ToString is not a recognized SQL operation, so I would assume its choking on that. — Maess, Feb 25 '14 at 13:13
I believe that `ToString()` cannot be converted to SQL. Which SQL types are the fields `LkpQualifier` and `xlkpQualifier`? — Howie, Feb 25 '14 at 13:13
I am getting Problem with ToString(), And I have to do conversion for the Join, Because LINQ doesn't Emplicitly converts data. — Rahul, Feb 25 '14 at 13:15

Howie · Answer 1 · 2014-02-25T13:28:27.443

Until you provide us with some more information on the SQL types of the fields LkpQualifier and xlkpQualifier, I will answer as follows:

You should either (1) change your table data schema and set the same data types or (2) write the raw SQL version of your LINQ query and execute the query

Raw SQL query using EF:

using (var db = new Entities())
{
    string myQuery = "SELECT * FROM Table";

    var result = new ObjectQuery<DbDataRecord>(myQuery, db);
    var resultList = result.ToList();
}

In the raw SQL query itself, you will probably have to use the CAST function with the right type:

CAST(Enumerations.xlkpQualifier AS System.Int32)

Raw SQL query without using EF:

There is also a way to write raw SQL without using EF (useful when your table doesn't have the primary key set and you don't have access to change that):

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();

    using (var cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM Table";

        var result = cmd.ExecuteScalar(); // or other variations
    }
}

To String Conversion while doing Join in LINQ

1 Answers1

Raw SQL query using EF:

Raw SQL query without using EF: