My first try at converting from LINQ to SQL to LINQ to Entity

Question

The program does some very simple lookups to generate data for a flashcard program which supports Mandarin Chinese and English

I deleted the .dbml file and created a Linq to Entities instead.

The original lookup was this:

using (var ceDictionary = new CeDictDataContext(Properties.Settings.Default.ChineseStudyConnection))
            {
                var definitions = from ed in ceDictionary.CeDicts
                                    where ed.Char == WorkTraditional.Text
                                    where ed.Bopo == foundBo[0]
                                    select ed;

                try
                {
                    foreach (var definition in definitions)
                    {
                        textDefinition.Text = definition.English;
                        break;
                    }
                }
                catch (System.Data.StrongTypingException eng)
                {
                    Status.Text = eng.Message;
                    textDefinition.Text = @"DBNull";
                }
            }

After a bit of head scratching and documentation browsing, I decided I only need to update the using statement and change the 'select ed' to a 'select new'

Like this:

using (var ceDictionary = new ChineseStudyEntities())
            {
                var definitions = from ed in ceDictionary.CeDicts
                    where ed.Char == WorkTraditional.Text
                    where ed.Bopo == foundBo[0]
                    select new
                    {
                        ed.English
                    };

                try
                {
                    foreach (var definition in definitions)
                    {
                        textDefinition.Text = definition.English;
                        break;
                    }
                }
                catch (System.Data.StrongTypingException eng)
                {
                    Status.Text = eng.Message;
                    textDefinition.Text = @"DBNull";
                }
            }

The good news is: the compiler was happy and the project builds without errors.

The bad news is: the foreach statement crashes at runtime with this (unintelligible to me) error message: Message=LINQ to Entities does not recognize the method 'System.String get_Item(Int32)' method, and this method cannot be translated into a store expression.

It is unintelligible because the one column I'm pulling out of the database is a string and I cannot begin to guess why "get_Item(Int32)" is somehow involved. (The two columns in the 'where' clauses are also strings.)

Answer 1

I think that your problem is with this part: foundBo[0]. Accessing letters by index cannot be translated to common SQL.

Try using Substring instead:

var definitions = from ed in ceDictionary.CeDicts
    where ed.Char == WorkTraditional.Text
    where ed.Bopo == foundBo.Substring(0, 1)
    select ed;