How do I use SqlFunctions.SoundCode on an Entity Framework insert?

Question

I am trying to insert an object into my database using Entity Framework where two of the properties represent the result of the SQL Server soundex function, and I can't find anything pointing me in the right direction.

A vastly simplified example of my code:

C# Object

public class Person{
    string FirstName
    string LastName
    string FirstNameSE
    string LastNameSE
}

Table Structure:

FirstName   varchar(50)
LastName    varchar(50)
FirstNameSE varchar(4)
LastNameSE  varchar(4)

Insert Method

public static void InsertIntoDatabase(Person vPerson){
    using (var db = new DatabaseContext()) {
        db.People.Add(vPerson);
        db.SaveChanges();
    }
}

This works, but I need FirstNameSE and LastNameSE to be set to soundex(FirstName) and soundex(LastName) respectively, I can't figure out how to do that on the insert.

I've found out how I can use the SqlFunctions class in LINQ to Entities select statements, but that doesn't help me when I'm trying to insert data.

Answer 1

I think you might be stuck making a couple round trips. Something like this might suffice (error handling elided for brevity):

public static void InsertIntoDatabase(Person vPerson){
    using (var db = new DatabaseContext()) {
        var soundExQuery = db.People.Select(p => 
              new { 
                      FirstNameSE = SqlFunctions.SoundCode(vPersion.FirstName), 
                      LastNameSE = SqlFunctions.SoundCode(vPersion.LastName) 
            }
        ).Take(1);

        var result = soundExQuery.ToArray();

        vPerson.FirstNameSE = result[0].FirstNameSE;
        vPersion.LastNameSE = result[0].LastNameSE;
        db.People.Add(vPerson);
        db.SaveChanges();
    }
}

Make one round trip to get the sound codes, then insert them.

Yes, its not very clean, but I think your only other option is to drop to straight SQL.

db.ExecuteStoreCommand(
    @"INSERT INTO People (FirstName, LastName, FirstNameSE, LastNameSE) 
    VALUES ({0}, {1}, SOUNDEX({0}), SOUNDEX({1}))"
    , vPerson.FirstName, vPerson.LastName);

Note that ExecuteStoreCommand does SQL Parameterization automatically. (its not a string.Format).

http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.executestorecommand.aspx