Convert string to decimal in group join linq query

You can do this by creating some Model-Defined Functions. See this link: Creating and Calling Model-Defined Functions in at least Entity Framework 4

Specifically, to add some functions to convert string to decimal and string to int, follow these steps:

Open your .EDMX file as XML so you can edit the text.

Add your custom conversion functions to the "CSDL content" section's "Scheme" section

<edmx:ConceptualModels><Schema....>

New functions:

<Function Name="ConvertToInt32" ReturnType="Edm.Int32">  <Parameter Name="myStr" Type="Edm.String" />  <DefiningExpression>    CAST(myStr AS Edm.Int32)  </DefiningExpression></Function><Function Name="ConvertToDecimal" ReturnType="Edm.Decimal">  <Parameter Name="myStr" Type="Edm.String" />  <DefiningExpression>    CAST(myStr AS Edm.Decimal(12, 2))  </DefiningExpression></Function>

(Modify the precision of the above Edm.Decimal to suit your needs.)

Then, in your c# code you need to create the corresponding static methods which you can store in a static class:

// NOTE: Change the "EFTestDBModel" namespace to the name of your model[System.Data.Objects.DataClasses.EdmFunction("EFTestDBModel", "ConvertToInt32")]public static int ConvertToInt32(string myStr){    throw new NotSupportedException("Direct calls are not supported.");}// NOTE: Change the "EFTestDBModel" namespace to the name of your model[System.Data.Objects.DataClasses.EdmFunction("EFTestDBModel", "ConvertToDecimal")]public static decimal ConvertToDecimal(string myStr){    throw new NotSupportedException("Direct calls are not supported.");}

Finally, to make calls to your new methods:

using (var ctx = new EFTestDBEntities()){    var results = from x in ctx.MyTables                  let TheTotal = ctx.MyTables.Sum(y => ConvertToDecimal(y.Price))                  select new                  {                      ID = x.ID,                      // the following three values are stored as strings in DB                      Price = ConvertToDecimal(x.Price),                      Quantity = ConvertToInt32(x.Quantity),                      Amount = x.Amount,                      TheTotal                  };}

Your specific example would look like this:

from p in db.TPsjoin n in db.TNson p.Key equals n.Keywhere (ConvertToDecimal(p.Value) ==         db.TNs.Where( nn => nn.Key == p.Key ).Sum( nn=> ConvertToDecimal(kk.Value)))

Unfortunately LINQ to SQL cannot create a SQL expression with a string to decimal conversion.

If you want to do this you have to execute your own query using:

ExecuteStoredQuery

Instead of converting the string to decimal, you could convert the decimal to string. This approach could work if the others do not.