Looking for a way to cast with entity framework 4

Question

Because of new requirements, we must add same translations to the current development, built in Entity-Framework 4, and it uses SQL Server. So, if we have an alert, for example, we need to bring the data by checking on translation some values, such as its title or description.

Table:

Alert

AlertId int
AlertTypeId int
AlertTextEnum int

Translation

FieldId int
FieldName string
Code string
Description string
LanguageId int

So, if we want to get the text for AlertType, we have to join both tables and make a relation with Translation.FieldName == "AlertTypeEnum", and Translation.Code == Alert.AlertTypeEnum. Mainly, this is the way.

Here my dirty approach checked on LinqPad but does not work with this version of Entity-Framework, because I cannot use the ToString method.

var o = Translations.Where(m => m.LanguageId == 1 && new List<string> {
                        "AlertTypeId"
                    }.Contains(m.FieldName));

 var list = from item in Alerts
            itemTranslation in  o item.AlertTypeId.ToString() equals itemTranslation.Code
                                    into ps
                                    from itemTranslation in ps.DefaultIfEmpty()             
            select new 
            {
                             AlertId = item.AlertId , 
                             AlertTypeDescr = itemTranslation.Description
            }.ToList();

Because this doesn't work, I'd tried to bring before the first query and parse Code into an integer, but it doesn't work either.

var o = Translations.Where(m => m.LanguageId == 1 && new List<string> {
                    "AlertTypeId"
                }.Contains(m.FieldName)).
                Select (x => new {
                   Code = (int)( x.Code),
                   FieldName = x.FieldName,
                   Description = x.Description
                }).ToList();

var list = from item in Alerts
                         join itemTranslation in  o
                              on   item.AlertTypeId equals itemTranslation.Code 
                         select new 
                         {
                                       AlertId = item.AlertId , 
                                 AlertTypeDescr = itemTranslation.Description
   };

I get "Cannot convert type 'string' to 'int'".

Some advice? I cannot change the version of entity framework and the models.

EF is converting your query to SQL, so you can only use operation which have equivalent functionality in SQL. You can use SqlFunctions provided by ef to do such operation, in your particular case SqlFunctions.StringConvert might help. Also see https://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-met — madan, Jan 04 '18 at 09:44

lucky · Answer 1 · 2018-01-04T11:33:15.553

Linq to Entities is translating your query to SQL script and there is no equilevant for casting string to int. If I'm not mistaken, there is no casting method for string to int in the SqlFunctions. So, I think you should change your approach and retrieve the data without any casting/converting operation in original. It would be better for the query performance also.

You can perform casting operation in memory and you can cast it on property level.

Also, I suggest you to use known types instead of anonymous type to select them.

So, create a class;

public class TranslationModel
{

    public int Code
    {
        get
        {
            //Parse string
            if (!int.TryParse(CodeStr, out var val))
            {
                val = -1;
            }
            return val;
        }
    }

    public string CodeStr { get; set; }

    public string FieldName { get; set; }

    public string Description { get; set; }
}

Change your query;

var filterList = new List<string>{ "AlertTypeId" };
var o = Translations.Where(m => m.LanguageId == 1 && filterList.Contains(m.FieldName))
    .Select(x => new TranslationModel
    {
        CodeStr = x.Code,
        FieldName = x.FieldName,
        Description = x.Description
    }).ToList();

Finally, you can use int form of Code using Code property in the TransactionModel.

First of all, thanks for your reply. Unfortunately, this code cannot run because we cannot retrieve a list and then use it in other query except "Contains" operation: {System.NotSupportedException: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator. at System.Data.Linq.SqlClient.QueryConverter.CoerceToSequence(SqlNode node) — Daniel 976034, Jan 04 '18 at 11:16

Looking for a way to cast with entity framework 4

Alert

Translation

1 Answers1