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)

did you tried with `Convert.ToDecimal` instead of `decimal.Parse`? — Yograj Gupta, Aug 31 '12 at 13:17
@YograjGupta - Why should that help? SQL Server will not know about `Convert` either. — Oded, Aug 31 '12 at 13:18
because when you will use Convert.ToDecimal it will generate sql like CONVERT(Decimal(29,4),[t0].[value]), so try this. — Yograj Gupta, Aug 31 '12 at 13:22
Perhaps this answer will help: [Convert string to int in EF 4](http://stackoverflow.com/questions/5754218/convert-string-to-int-in-ef-4-0) — Brad Rem, Aug 31 '12 at 13:27
@majkinetor, It is working, for testing I use this linq `from x in db.TestStringToDecimals select new { x.Id, val= Convert.ToDecimal(x.Value)}` and it is producing sql `SELECT [t0].[id] AS [Id], CONVERT(Decimal(29,4),[t0].[value]) AS [val] FROM [TestStringToDecimal] AS [t0]` for me. — Yograj Gupta, Aug 31 '12 at 13:46
I get this: base {System.SystemException} = {"LINQ to Entities does not recognize the method 'System.Decimal ToDecimal(System.String)' method, and this method cannot be translated into a store expression."} — majkinetor, Aug 31 '12 at 14:28
This is what I did: from p in db.TP where (Convert.ToDecimal(p.Value) == 33) select p; — majkinetor, Aug 31 '12 at 14:29
This is related: http://stackoverflow.com/questions/992189/c-sharp-linq-to-sql-how-to-express-convert-as-int — majkinetor, Sep 01 '12 at 06:19

score 10 · Answer 1 · answered Aug 31 '12 at 14:49

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)))

@majkinetor, if you are using Code First, why not generate your database fields as decimals in your code first class in the first place? — Brad Rem, Sep 03 '12 at 11:44
Its existing database. Do you mean that if I set it anyway, code first will do automatic conversion ? — majkinetor, Sep 04 '12 at 05:29
@majkinetor, I don't think so. I'm not that familiar with working with EF in Code First with an existing database, but you may want to look at [Entity Framework Power Tools](http://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d) to see if there is something that will make things easier for you. — Brad Rem, Sep 04 '12 at 22:55
I don't know why the EF team haven't implemented this in the framework yet, with the solution being as simple as this. Thanks for the code. — Adam, Jan 30 '14 at 03:05

score 0 · Answer 2 · answered Aug 31 '12 at 13:21

0

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

answered Aug 31 '12 at 13:21

Nick Jones

6,413
2
18
18

Meh... some framework... The problem here is losing convenience. I am creating anonymous results while ExecuteStoredQuery expects type – majkinetor Aug 31 '12 at 13:26

score 0 · Answer 3 · answered Aug 31 '12 at 13:24

0

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

answered Aug 31 '12 at 13:24

Thom Smith

13,916
6
45
91

Convert string to decimal in group join linq query

3 Answers3

Linked