none
passing a string to get a linq field RRS feed

  • Question

  • Hello

    I need to do something like this

     

    Dim fieldName As String = "MyFieldName"
    Dim Data =( From p In database.table1 Where id = 10 Select p).signle
    Dim wantedvalue = Data("MyFieldName")

    is it possible to pass the field name as a string instead of
    Dim wantedvalue = Data.MyFieldName???????

    Wednesday, April 29, 2009 11:08 PM

Answers

  • Yet another option is to use System.Linq.Dynamic to (at runtime) create a query that does all the calculations + transformations + projections for you.

    I once built a report query engine on top of it, allowing calculation formulas etc to be defined in the database and put together at runtime.

    The nice thing is of course that all the 'work' can then pushed back to the database (or processed locally as a linq-to-objects query if you prefer to do it on the client). Also, you don't need to pull back any data that is not needed/used.

    E.g.:

    //get hold of the source table
    Type reportTable = Type.GetType("MarketTransaction");
    IQueryable query = someDataContext.GetTable(reportTable);
    
    //add a where clause
    string paramString = "TransactionCategory.Code=@0 && ValueDateUntil>=@1 && ValueDateUntil<=@2";
    object[] paramValues = { "FX", DateTime.Parse("2009-04-01 00:00:00"), DateTime.Parse("2009-04-30 23:59:59") };
    query = query.Where(paramString, paramValues.ToArray());
    
    //do a couple of groupings
    groupByFields = "Broker.Code as BrokerCode, Currency.Code as CurrencyCode, PricingCurrency.Code as PricingCurrencyCode";
    query = query.GroupBy("new (" + groupByFields + ")", "it");
    
    //sort
    orderByFields = "Key.BrokerCode";
    query = query.OrderBy(orderByFields);
    
    //and select/project
    selectedFields 
       = "Key.BrokerCode as BrokerCode, Sum(((TransactionTypeCode==\"BUY\") ? RemainingUnits : 0)) as BoughtFX, "
       + "Sum(((TransactionTypeCode==\"SELL\") ? -RemainingUnits : 0)) as SellFX, "
       + "Sum(((TransactionTypeCode==\"BUY\") ? RemainingUnits : -RemainingUnits)) as RemainingFX, "
       + "Key.CurrencyCode as CurrencyCode, "
       + "Sum(((TransactionTypeCode==\"BUY\") ? RemainingPosition : 0)) as BoughtValueLocal, "
       + "Sum(((TransactionTypeCode==\"SELL\") ? -RemainingPosition : 0)) as SoldValueLocal, "
       + "Sum(((TransactionTypeCode==\"BUY\") ? RemainingPosition : -RemainingPosition )) as TotalRemaining, "
       + "Key.PricingCurrencyCode as PricingCurrencyCode";
    IList results = query.Select("new ( " + selectedFields + " )").ToList();
    


    ...results in a db query like this:

    SELECT [t5].[broker_code] AS [BrokerCode], [t5].[value] AS [BoughtFX], [t5].[value2] AS [SellFX], [t5].[value3] AS [RemainingFX], [t5].[currency_code] AS [CurrencyCode], [t5].[value4] AS [BoughtValueLocal], [t5].[value5] AS [SoldValueLocal], [t5].[value6] AS [TotalRemaining], [t5].[currency_code2] AS [PricingCurrencyCode]
    FROM (
        SELECT SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p3 THEN CONVERT(Decimal(34,5),[t0].[remaining_units])
                ELSE CONVERT(Decimal(34,5),@p4)
             END)) AS [value], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p5 THEN CONVERT(Decimal(34,5),-[t0].[remaining_units])
                ELSE CONVERT(Decimal(34,5),@p6)
             END)) AS [value2], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p7 THEN [t0].[remaining_units]
                ELSE -[t0].[remaining_units]
             END)) AS [value3], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p8 THEN CONVERT(Decimal(34,5),[t0].[remaining_position])
                ELSE CONVERT(Decimal(34,5),@p9)
             END)) AS [value4], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p10 THEN CONVERT(Decimal(34,5),-[t0].[remaining_position])
                ELSE CONVERT(Decimal(34,5),@p11)
             END)) AS [value5], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p12 THEN [t0].[remaining_position]
                ELSE -[t0].[remaining_position]
             END)) AS [value6], [t2].[broker_code], [t3].[currency_code], [t4].[currency_code] AS [currency_code2]
        FROM [dbo].[market_transaction] AS [t0]
        INNER JOIN [dbo].[transaction_category] AS [t1] ON [t1].[transaction_category_code] = [t0].[transaction_category_code]
        INNER JOIN [dbo].[broker] AS [t2] ON [t2].[broker_id] = [t0].[broker_id]
        LEFT OUTER JOIN [dbo].[currency] AS [t3] ON [t3].[currency_code] = [t0].[currency_code]
        LEFT OUTER JOIN [dbo].[currency] AS [t4] ON [t4].[currency_code] = [t0].[pricing_currency_code]
        WHERE ([t1].[transaction_category_code] = @p0) AND ([t0].[value_date_until] >= @p1) AND ([t0].[value_date_until] <= @p2)
        GROUP BY [t2].[broker_code], [t3].[currency_code], [t4].[currency_code]
        ) AS [t5]
    ORDER BY [t5].[broker_code]


    ...and the resulting IList of (runtime-generated entity projections) can then be handed to a grid or some other UI control for display.

    You can read more about System.Linq.Dynamic here: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools
    Thursday, April 30, 2009 9:33 AM
    Answerer

All replies

  • Reflection:

    public static object GetPropertyValue(object obj, string propName)
    {
        System.Reflection.PropertyInfo propInfo = obj.GetType().GetProperty(propName);
        return propInfo.GetValue(obj, null);
    }

    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools
    Thursday, April 30, 2009 1:06 AM
    Answerer
  • how about performance side?
    Thursday, April 30, 2009 6:12 AM
  • Using System.Reflection.PropertyInfo to access properties is slower than directly accessing properties, yes. But are you retrieving a lot of properties? If not, I wouldn't worry about it...   ...if you're retrieving a record from the database and then reading a few properties using PropertyInfo, the reflection part is negligible compared to the db roundtrip.


    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools
    Thursday, April 30, 2009 6:24 AM
    Answerer
  • Well I have to get the data from the database and then iterate trough them

    the problem is that each time the fields where i have to iterate through is different
    and I am a bit scared that with this approach everything will be quite slow


    can you advice me on the best way to iterate trough values obtained with linq


    with a datase it was very easy

    For each row in dataset.table1
    dim a=row("FieldName")
    next

    where the fieldname is not know till run time

    do you know if i can acheave something like this with linq?

    Thursday, April 30, 2009 6:39 AM
  • How many records/objects and fields/properties are we talking about? And in what kind of operation? Unless you're doing hundreds of thousands of lookups you (and the users) are probably not going to notice the extra cost of using reflection.

    The db roundtrip and materializing the data into entity objects is most likely much more expensive than the property lookup anyway (regardless of if it is a small or large dataset). Run a quick test with a realistic set of data and measure how much time is spent on each operation...

    As a comparison, on my dev machine (a centrino duo notebook) debug compiled code adds ~.008ms (8ns) to each reflect property lookup done in a tight loop of 1 million objects compared to doing the same and directly referencing the same properties. Getting the million records from the db and materializing into objects takes a lot longer on the same system... :)


    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools
    Thursday, April 30, 2009 7:20 AM
    Answerer
  • 1) The application  retreves a bulk of data from the database
    2) Each line is sent to a class and here   some fields are read   

    so i have to get the property every time a read a line


    i dont know wich field must be read till run time therefore i need to create someting flexible
     

    Thursday, April 30, 2009 7:28 AM
  • 1) Get Bulk Data from database
    2) Send each line to a class
    3) The class has to extract some fields (not know till run times) and perform some speficic operation on them
    Thursday, April 30, 2009 7:33 AM
  • Do the combination of fields (and types) vary a lot, or is it just different names for essentially the same datatypes and/or operations?

    There could (possibly) be some savings to do by doing the 'lookup' part as a projection into a pre-defined class so the loop always work with the same class and members. Just that the projection is done at runtime.

    Either way, for [performance critical] bulk operations covering large amounts of data you need to question if using an OR mapper in the first place is the right thing to do.

    Also, what will you do with the bulk data after processing? Write something back to the database?


    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools
    Thursday, April 30, 2009 7:44 AM
    Answerer
  • Yes there is a large variation of fields involved  and the operation on each field are completely differents

    i wanted to avoid to create mapped class

    i can not believe that link does not allow to access a cell in a row by passing its name as string

    no i will not write thedata back to teh database  they are used just to display the results
    Thursday, April 30, 2009 7:55 AM
  • Yet another option is to use System.Linq.Dynamic to (at runtime) create a query that does all the calculations + transformations + projections for you.

    I once built a report query engine on top of it, allowing calculation formulas etc to be defined in the database and put together at runtime.

    The nice thing is of course that all the 'work' can then pushed back to the database (or processed locally as a linq-to-objects query if you prefer to do it on the client). Also, you don't need to pull back any data that is not needed/used.

    E.g.:

    //get hold of the source table
    Type reportTable = Type.GetType("MarketTransaction");
    IQueryable query = someDataContext.GetTable(reportTable);
    
    //add a where clause
    string paramString = "TransactionCategory.Code=@0 && ValueDateUntil>=@1 && ValueDateUntil<=@2";
    object[] paramValues = { "FX", DateTime.Parse("2009-04-01 00:00:00"), DateTime.Parse("2009-04-30 23:59:59") };
    query = query.Where(paramString, paramValues.ToArray());
    
    //do a couple of groupings
    groupByFields = "Broker.Code as BrokerCode, Currency.Code as CurrencyCode, PricingCurrency.Code as PricingCurrencyCode";
    query = query.GroupBy("new (" + groupByFields + ")", "it");
    
    //sort
    orderByFields = "Key.BrokerCode";
    query = query.OrderBy(orderByFields);
    
    //and select/project
    selectedFields 
       = "Key.BrokerCode as BrokerCode, Sum(((TransactionTypeCode==\"BUY\") ? RemainingUnits : 0)) as BoughtFX, "
       + "Sum(((TransactionTypeCode==\"SELL\") ? -RemainingUnits : 0)) as SellFX, "
       + "Sum(((TransactionTypeCode==\"BUY\") ? RemainingUnits : -RemainingUnits)) as RemainingFX, "
       + "Key.CurrencyCode as CurrencyCode, "
       + "Sum(((TransactionTypeCode==\"BUY\") ? RemainingPosition : 0)) as BoughtValueLocal, "
       + "Sum(((TransactionTypeCode==\"SELL\") ? -RemainingPosition : 0)) as SoldValueLocal, "
       + "Sum(((TransactionTypeCode==\"BUY\") ? RemainingPosition : -RemainingPosition )) as TotalRemaining, "
       + "Key.PricingCurrencyCode as PricingCurrencyCode";
    IList results = query.Select("new ( " + selectedFields + " )").ToList();
    


    ...results in a db query like this:

    SELECT [t5].[broker_code] AS [BrokerCode], [t5].[value] AS [BoughtFX], [t5].[value2] AS [SellFX], [t5].[value3] AS [RemainingFX], [t5].[currency_code] AS [CurrencyCode], [t5].[value4] AS [BoughtValueLocal], [t5].[value5] AS [SoldValueLocal], [t5].[value6] AS [TotalRemaining], [t5].[currency_code2] AS [PricingCurrencyCode]
    FROM (
        SELECT SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p3 THEN CONVERT(Decimal(34,5),[t0].[remaining_units])
                ELSE CONVERT(Decimal(34,5),@p4)
             END)) AS [value], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p5 THEN CONVERT(Decimal(34,5),-[t0].[remaining_units])
                ELSE CONVERT(Decimal(34,5),@p6)
             END)) AS [value2], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p7 THEN [t0].[remaining_units]
                ELSE -[t0].[remaining_units]
             END)) AS [value3], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p8 THEN CONVERT(Decimal(34,5),[t0].[remaining_position])
                ELSE CONVERT(Decimal(34,5),@p9)
             END)) AS [value4], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p10 THEN CONVERT(Decimal(34,5),-[t0].[remaining_position])
                ELSE CONVERT(Decimal(34,5),@p11)
             END)) AS [value5], SUM(
            (CASE 
                WHEN [t0].[transaction_type_code] = @p12 THEN [t0].[remaining_position]
                ELSE -[t0].[remaining_position]
             END)) AS [value6], [t2].[broker_code], [t3].[currency_code], [t4].[currency_code] AS [currency_code2]
        FROM [dbo].[market_transaction] AS [t0]
        INNER JOIN [dbo].[transaction_category] AS [t1] ON [t1].[transaction_category_code] = [t0].[transaction_category_code]
        INNER JOIN [dbo].[broker] AS [t2] ON [t2].[broker_id] = [t0].[broker_id]
        LEFT OUTER JOIN [dbo].[currency] AS [t3] ON [t3].[currency_code] = [t0].[currency_code]
        LEFT OUTER JOIN [dbo].[currency] AS [t4] ON [t4].[currency_code] = [t0].[pricing_currency_code]
        WHERE ([t1].[transaction_category_code] = @p0) AND ([t0].[value_date_until] >= @p1) AND ([t0].[value_date_until] <= @p2)
        GROUP BY [t2].[broker_code], [t3].[currency_code], [t4].[currency_code]
        ) AS [t5]
    ORDER BY [t5].[broker_code]


    ...and the resulting IList of (runtime-generated entity projections) can then be handed to a grid or some other UI control for display.

    You can read more about System.Linq.Dynamic here: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools
    Thursday, April 30, 2009 9:33 AM
    Answerer