none
Entity framework - select column from two related tables RRS feed

  • Question

  • Hi,

    I'm trying to select different columns from two related tables using a dynamic linq query.

    Here is my method:

    public object[] GetData(string select, string whereClause) { try { var Configurations = contractMediator.QueryContracts<IConfiguration>(); var Setups = contractMediator.QueryContracts<ISetup>(); var query = Configurations.Join<IConfiguration, ISetup, object, object>(Setups, // target c => c.Setup.ID, // FK cm => cm.ID, // PK (c, cm) => new { Configurations = c, Setups = cm }) .Select("new(" + select + ")") .Where(whereClause); if (!query.Any()) return null;

    else { object[] testResultsArray = new object[query.Count()]; int i = 0; foreach (var item in query) { testResultsArray[i] = (object)item; ++i; } return testResultsArray; } } catch (Exception e) { return null;

    } }

    I call the above method as below:

    GetData("IP", "IP == \"192.168.1.2\"")

    But I'm getting the following error message:

    No property or field 'IP' exists in type 'Object'

    But when change the query as below, the method works fine and retrieves the IP, I mean, no problem is found regarding the IP column:

    var query = Configurations.AsQueryable<IConfiguration>()
                                        .Select("new(" + select + ")")
                                        .Where(whereClause);

    Note: The Configuration table has a key to the Setup table.

    Can you please explain to me what is wrong in my method and how can I solve it?

    Regards,

    Nadeem

    Wednesday, April 10, 2013 1:04 PM

Answers

  • Hi Nadeem,

    Do you mean the exception is actually thrown from this query:

    var query = Configurations.Join<IConfiguration, ISetup, object, object>(Setups,     // target
                                                                                  c => c.Setup.ID,          // FK
                                                                                  cm => cm.ID,              // PK
                                                                                  (c, cm) => new { Configurations = c, Setups = cm })
                                        .Select("new(" + select + ")")
                                        .Where(whereClause);
    

    "(c, cm) => new { Configurations = c, Setups = cm }"  This statement returns the type of object, which does not contains the IP property, even Configurations and Setups. Dynamic LINQ will try to access the "IP" property of object, so the exception will be thrown.
    You may try to avoid using Dynamic LINQ in this scenario. foreach statement and dynamic type. I suppose the IP property is in the configuration, it should be something like:

    List<dynamic> list = new List<dynamic>();
    foreach (dynamic d in query)
    {
        var conf = d.Configurations;
        System.Reflection.PropertyInfo prop = conf.GetType().GetProperty("IP");
        string val = (string)prop.GetValue(c);
        if (val == "192.168.1.2")
        {
            list.Add(d);
        }
    }
    var arr = list.ToArray();

    There is another way to create your custom class contains the properties, and then create a new instance of this class in the Join method. But I think it has a high dependency:

    var query = Configurations.Join<IConfiguration, ISetup, object, MyClass>(Setups,     // target
                                                                                  c => c.Setup.ID,          // FK
                                                                                  cm => cm.ID,              // PK
                                                                                  (c, cm) => new MyClass{ Configurations = c, Setups = cm })
    

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, April 12, 2013 7:27 AM
    Moderator