Convert string to decimal in group join linq query

Question

I have to join two tables but to return only those records in second table where sum of 'Value' of all records associated with the record in first table is the same.

from p in db.TPs
join n in db.TNs
on p.Key equals n.Key
where (decimal.Parse(p.Value) == db.TNs.Where( nn => nn.Key == p.Key )
                                       .Sum( nn=> decimal.Parse(kk.Value)))

I'm using Entity Framework Code-First.

Of course, Linq complains

LINQ to Entities does not recognize the method 'System.Decimal Parse(System.String)' method

Tables are huge and I must reduce output, so doing this conversion on the client side is not possible. Column type conversion is also not an option.

The SQL query is:

select * from TP as p
join * from TN as n on n.Key = p.Key
where p.Value = (select sum(cast(n.Value as decimal(12,2))) from TN where Key = p.Key)

Answer 1

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.TPs
join n in db.TNs
on p.Key equals n.Key
where (ConvertToDecimal(p.Value) == 
        db.TNs.Where( nn => nn.Key == p.Key ).Sum( nn=> ConvertToDecimal(kk.Value)))