none
Variable values in IQueryable Where and Join RRS feed

  • Question

  • Hi,

    Is there any way the values ​​of the variables used in the function are placed as constants Linq to SQL? There is how to do this by assigning the result of commands Where and Join a IQueryable?

    Eg.:

    dbMyDatabase db = new dbMyDatabase();
    IQueryable<myTable> t1;
    IQueryable<myTable> t2 = from t in db.myTable select t;
    List<myTable> result = new List<myTable>();
    
    int[] arr1 = new int[]{1,2,3,4,5};
    int[] arr2 = new int[]{9,8,7,6,5};
    int var1, var2;
    
    for(int i = 0; i < arr1.Count(); i++){
    	var1 = arr1[i];
    	var2 = arr2[i];
    	t1 = t2.Where(e => e.col1 == var1 && e.col2 == var2);
    }
    
    result.AddRange(t1);

    The values ​​of variables var1 and var2 are only caught when the AddRange run, causing everyone Where / Join have the same values ​​in the parameters.

    Already gratefully
    Luiz Fernando Cruz
    Thursday, October 31, 2013 4:01 AM

Answers

  • Read my last post please, I construct another example.


    Your last code seems to give a compilation error at “t1=>t1” position. Can you re-check and post a small fragment that works?


    By the way, you did not follow the advice to declare var1 and var2 inside the loop. Sometimes this is important in case of LINQ.



    Thursday, October 31, 2013 8:31 PM

All replies

  • I think your problem is only a debugging issue and not a real problem.  The query is run automatically whenever t1 is used by another method which make perfect sense.  You don't need to run a query unless something in the code is using the results of the query.

    jdweng

    Thursday, October 31, 2013 6:34 AM
  • Your loop only keeps the values found in t2 on last iteration. Maybe you actually want to accumulate all of the values. Then try Concat and define the variables inside the loop, something like this:

    IEnumerable<myTable> result = new List<myTable>();
    for(int i = 0; i < arr1.Count(); i++)
    {
        int var1 = arr1[i];
        int var2 = arr2[i];
    
        result = result.Concat( t2.Where(e => e.col1 == var1 && e.col2 == var2));
    }


    • Edited by Viorel_MVP Thursday, October 31, 2013 7:00 AM
    Thursday, October 31, 2013 7:00 AM
  • You need to add the results in inside of the loop, not after it:

    for(int i = 0; i < arr1.Count(); i++)
    {
            var1 = arr1[i];
            var2 = arr2[i];
            t1 = t2.Where(e => e.col1 == var1 && e.col2 == var2);
                    result.AddRange(t1); // Add this to the result here
    }

    Reed Copsey, Jr. - http://reedcopsey.com - If a post answers your question, please click Mark As Answer on that post. If you find a post helpful, please click Vote as Helpful.
    Thursday, October 31, 2013 7:03 PM
    Moderator
  • Thanks for replies.

    Take a look in modified example:

    dbMyDatabase db = new dbMyDatabase();
    IQueryable<myTable> t1;
    IQueryable<myTable> t2 = from t in db.myTable select t;
    List<myTable> result = new List<myTable>();
    
    int[] arr1 = new int[]{1,2,3,4,5};
    int[] arr2 = new int[]{9,8,7,6,5};
    int var1, var2;
    
    var1 = arr1[0];
    var2 = arr2[0];
    t1 = t2.Where(e => e.col1 == var1 && e.col2 == var2);
    
    for(int i = 1; i < arr1.Count(); i++){
    	var1 = arr1[i];
    	var2 = arr2[i];
    	t1 = t1.Join(t2.Where(e => e.col1 == var1 && e.col2 == var2), t1 => t1, t2 => t2, (t1, t2) => t2);
    }
    
    result.AddRange(t1);

    Thats execute out of loop because I need performance.

    myTable have 6 million of registers.

    Sorry my bad english :-)

    Thursday, October 31, 2013 7:44 PM
  • I modified a example, read my last post please.
    Thursday, October 31, 2013 7:45 PM
  • Read my last post please, I construct another example.

    Thursday, October 31, 2013 7:46 PM
  • Read my last post please, I construct another example.

    Can you step back, and explain what is in your table, and what you're trying to fetch?

    That would make it easier to help you...


    Reed Copsey, Jr. - http://reedcopsey.com - If a post answers your question, please click Mark As Answer on that post. If you find a post helpful, please click Vote as Helpful.

    Thursday, October 31, 2013 7:56 PM
    Moderator
  • Read my last post please, I construct another example.


    Your last code seems to give a compilation error at “t1=>t1” position. Can you re-check and post a small fragment that works?


    By the way, you did not follow the advice to declare var1 and var2 inside the loop. Sometimes this is important in case of LINQ.



    Thursday, October 31, 2013 8:31 PM
  • I declare var1 and var2 inside the loop! It worked! Thanks!
    Friday, November 1, 2013 12:07 PM