Linq to entities / EF : join to tables on a decimal and a string field

Question

I want to join two tables, but the two join fields differ for the format: one is a decimal and one is a string.

Linq doesn't let me convert the decimal field to string with ToString() or the string field to decimal with Convert.ToDecimal() comand.

var tmp = from c in gge.GiftCards
          join p in gge.Customers
          on  c.OwnerId equals p.customer_Key
          into g
          from o in g.DefaultIfEmpty()
          select new MyCardViewModel {GiftCard = c, Customers= g};

Is there a way to accomplish this?

Answer 1

I'd be very much inclined to fix the database too. You don't say in your question which field is the string and which is the decimal, but either way I don't think that using a decimal makes sense as a key. It would be better if they were both strings, integers or GUIDs. Nevertheless you have a couple of other options.

The first option is you add a view or a stored proc that converts one of the fields before it gets to the EF.

The other option is to bring the smaller set of data into memory and cast via code. If I assume that GiftCards is the smaller set and that OwnerId is the decimal, then you could try this:

var gcs = gge.GiftCards
    .Select(gc => gc.OwnerId)
    .ToDictionary(x => x.OwnerId.ToString(), x => x);

var gcIds = gcs.Keys.ToArray();

var results = (
    from p in gge.Customers
    where gcIds.Contains(p.customer_Key)
    select p)
    .ToArray()
    .GroupBy(p => p.customer_Key)
    .Select(p => new MyCardViewModel
        {
            GiftCard = gcs[p.Key],
            Customers = p.ToArray(),
        });

Doing these queries will cause queries like the following to be executed:

SELECT ...
FROM [GiftCards] AS t0

SELECT ...
FROM [Customers] AS t0
WHERE t0.[customer_Key] IN ("1", "2", "3", ..., "1442")

Let me know if this works for you. Cheers.