none
How do I access columns by name using Entity Framework?

    Question

  • Hi!

    Have a table that may vary the number of columns.

    The columns are always in the following pattern: Name1, Address1, Phone1, Name2, Address2, Phone2, Name3, Address3, Telefone3, etc..

    I'm using Entity Framework, so I wanted something like this:

    public int Insert(int[][] paramArray){
    
    dbEntities db = new dbEntities();
    myTable obj = new myTable();
    
    for(int i = 0; i <= myTable.Columns.Count(); i++)
    {
        myTable.Columns.GetByName("Name" + i).Value = paramArray[i][0];
        myTable.Columns.GetByName("Address" + i).Value = paramArray[i][1];
        myTable.Columns.GetByName("Phone" + i).Value = paramArray[i][2];
    }
    
    db.myTable.Add(obj);
    db.SaveChanges();
    
    return obj.id;
    
    }

    Of course this is my code wrong, it's just so you understand what I want.

    Thanks!

    LFC

    Monday, September 23, 2013 10:09 PM

Answers

  • Hello Luiz Fernando Cruz,

    From my opinion, I do not think it is possible if we just use the entity framework.

    For achieving what you want, my suggestion is to write the assignment logic to be string text and the compile them at runtime like below:

    string executeString = string.Empty;
    
    
                    EntityType type = ((IObjectContextAdapter)db).ObjectContext.MetadataWorkspace.GetItems<EntityType>(DataSpace.CSpace).Where(x => x.Name == "myTable").FirstOrDefault();
    
    
                    for (int i = 0; i < type.Properties.Count(); i++)
    
                    {
    
                        if (type.Properties[i].Name.Contains("Name"))
    
                        {
    
                            executeString += "myTable." + type.Properties[i].Name + "=paramArray[i][0];";
    
                        }
    
                        if (type.Properties[i].Name.Contains("Address"))
    
                        {
    
                            executeString += "myTable." + type.Properties[i].Name + "=paramArray[i][0];";
    
                        }
    
                        if (type.Properties[i].Name.Contains("Phone"))
    
                        {
    
                            executeString += "myTable." + type.Properties[i].Name + "=paramArray[i][0];";
    
                        }
    
                    }
    
    
                    var csc = new CSharpCodeProvider(new Dictionary<string, string>() { { "CompilerVersion", "v3.5" } });
    
                    var parameters = new CompilerParameters(new[] { "mscorlib.dll", "System.Core.dll" }, "Foo.exe", true);
    
                    parameters.GenerateExecutable = true;
    
                    CompilerResults results = csc.CompileAssemblyFromSource(parameters,
    
                    @"
    
    namespace Foo
    
    {
    
        public class Bar
    
        {
    
            public int Insert(int[][] paramArray){
    
    
            dbEntities db = new dbEntities();
    
            myTable obj = new myTable();
    
            
    
            "+executeString+@"
    
    
            db.myTable.Add(obj);
    
            db.SaveChanges();
    
    
            return obj.id;
    
    
            }
    
        }
    
    }
    
    
    ");
    
                    if (results.Errors.Count != 0)
    
                        throw new Exception("Mission failed!");
    
    
                    object o = results.CompiledAssembly.CreateInstance("Foo.Bar");
    
                    MethodInfo mi = o.GetType().GetMethod("Insert(" + paramArray + ")");
    
                    mi.Invoke(o, null);
    

    More information about compile code at runtime:

    http://blogs.msdn.com/b/ddietric/archive/2008/06/11/compiling-code-at-runtime-c-3-0-and-compiler-executable-file-csc-exe-cannot-be-found.aspx

    Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, September 25, 2013 7:22 AM
    Moderator

All replies

  • Monday, September 23, 2013 11:52 PM
  • It does not work.

    I need something like:

    p.CategoryID1 = x;

    p.CategoryID2 = x;

    p.CategoryID3 = x;

    The change occurs in the name of the columns.

    Tuesday, September 24, 2013 12:08 AM
  • Hi Liuz,

    Thanks for your posting.

    From your description, I think that this issue is related to Entity Framework,Data Platform Development forums  are more suitable for it. So I will move it to Data Platform Development forums .

    Data Platform Development forums:

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    Hope these help.


    Lilia Gong <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Tuesday, September 24, 2013 4:04 PM
  • Hello Luiz Fernando Cruz,

    From my opinion, I do not think it is possible if we just use the entity framework.

    For achieving what you want, my suggestion is to write the assignment logic to be string text and the compile them at runtime like below:

    string executeString = string.Empty;
    
    
                    EntityType type = ((IObjectContextAdapter)db).ObjectContext.MetadataWorkspace.GetItems<EntityType>(DataSpace.CSpace).Where(x => x.Name == "myTable").FirstOrDefault();
    
    
                    for (int i = 0; i < type.Properties.Count(); i++)
    
                    {
    
                        if (type.Properties[i].Name.Contains("Name"))
    
                        {
    
                            executeString += "myTable." + type.Properties[i].Name + "=paramArray[i][0];";
    
                        }
    
                        if (type.Properties[i].Name.Contains("Address"))
    
                        {
    
                            executeString += "myTable." + type.Properties[i].Name + "=paramArray[i][0];";
    
                        }
    
                        if (type.Properties[i].Name.Contains("Phone"))
    
                        {
    
                            executeString += "myTable." + type.Properties[i].Name + "=paramArray[i][0];";
    
                        }
    
                    }
    
    
                    var csc = new CSharpCodeProvider(new Dictionary<string, string>() { { "CompilerVersion", "v3.5" } });
    
                    var parameters = new CompilerParameters(new[] { "mscorlib.dll", "System.Core.dll" }, "Foo.exe", true);
    
                    parameters.GenerateExecutable = true;
    
                    CompilerResults results = csc.CompileAssemblyFromSource(parameters,
    
                    @"
    
    namespace Foo
    
    {
    
        public class Bar
    
        {
    
            public int Insert(int[][] paramArray){
    
    
            dbEntities db = new dbEntities();
    
            myTable obj = new myTable();
    
            
    
            "+executeString+@"
    
    
            db.myTable.Add(obj);
    
            db.SaveChanges();
    
    
            return obj.id;
    
    
            }
    
        }
    
    }
    
    
    ");
    
                    if (results.Errors.Count != 0)
    
                        throw new Exception("Mission failed!");
    
    
                    object o = results.CompiledAssembly.CreateInstance("Foo.Bar");
    
                    MethodInfo mi = o.GetType().GetMethod("Insert(" + paramArray + ")");
    
                    mi.Invoke(o, null);
    

    More information about compile code at runtime:

    http://blogs.msdn.com/b/ddietric/archive/2008/06/11/compiling-code-at-runtime-c-3-0-and-compiler-executable-file-csc-exe-cannot-be-found.aspx

    Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, September 25, 2013 7:22 AM
    Moderator