0

I am trying to count the VALUEs corresponding to a List<int[]> using Linq to Entity Framework.

  1. I have a List<int[]> where each int[] in the List is of length 2.
  2. I have a DB table VALUES which contains 3 columns, called ID, PARENT and VALUE, where each int[] in the List (see 1) may correspond to a record in the table, the 0 index being ID and the 1 index being PARENT. *But some arrays likely do not correspond to any existing records in the table.

Each combination of ID and PARENT correspond to multiple DB records, with different VALUEs.

Several points that are important to note:

  1. One of the problems is that I can't rely on ID alone - each value is defined/located according to both the ID and PARENT.

  2. None of the int arrays repeat, though the value in each index may appear in several arrays, e.g.

    List<int[]> myList = new List<int[]>();
myList.add(new int[]{2, 1});
myList.add(new int[]{3, 1}); //Notice - same "PARENT"
myList.add(new int[]{4, 1}); //Notice - same "PARENT"
myList.add(new int[]{3, 1}); //!!!! Cannot exist - already in the List

I can't seem to figure out how to request all of the VALUEs from the VALUES table that correspond to the ID, PARENT pairs in the List<int[]>.

I've tried several variations but keep arriving at the pitfall of attempting to compare an array in a linq statement... I can't seem to crack it without loading substantially more information that I actually need.

Probably the closest I've gotten is with the following line:

var myList = new List<int[]>();
// ... fill the list ...
var res = myContext.VALUES.Where(v => myList.Any(listItem => listItem[0] == v.ID && listItem[1] == v.PARENT));

Of course, this can't work because The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities.

@chris huber

I tried it out but it was unsuccessful.
2 things:

  1. Where you created "myValues" I have a DB table entity, not a List.
  2. Due to point number 1, I am using LINQ to Entities, as opposed to LINQ to Object

My code then comes to something like this:

var q2 = from value in myContext.VALUES where myList.Select(x => new { ID = x.ElementAt(0), Parent = x.ElementAt(1) }).Contains(new { ID = value.ID, Parent = value.PARENT }) select value;

This returns the following error message when run:

LINQ to Entities does not recognize the method 'Int32 ElementAt[Int32](System.Collections.Generic.IEnumerable` 1[System.Int32],Int32)' method, and this method cannot be translated into a store expression.

@Ovidiu

I attempted your solution as well but the same problem as above:

As I am using LINQ to Entities, there are simply certain things that cannot be performed, in this case - the ToString() method is "not recognized". Removing the ToString() method and attempting to simply have a Int32 + "|" + In32 gives me a whole other error about LINQ to Entities not being able to cast an Int32 to Object.

Guy Passy
  • 694
  • 1
  • 9
  • 32

2 Answers2

0

You could use this workaround: create a new List<string> from your List<int[]> and compare the values in your table with the new list. I didn't test this with EF, but it might work

List<string> strList = myList.Select(x => x[0].ToString() + "|" + x[1].ToString()).ToList();
var res = myContext.VALUES.Where(x => strList.Contains(SqlFunctions.StringConvert((double)x.ID).Trim() + "|" + SqlFunctions.StringConvert((double)x.PARENT).Trim()));
Ovidiu
  • 1,407
  • 12
  • 11
  • I updated the question with the result of my attempt to use your suggested work-around. – Guy Passy Sep 17 '13 at 15:03
  • Found a replacement for ToString() [here](http://stackoverflow.com/questions/1066760/problem-with-converting-int-to-string-in-linq-to-entities). Instead of `x.ID.ToString()` use `SqlFunctions.StringConvert((double)x.ID).Trim()` – Ovidiu Sep 17 '13 at 15:18
  • Does this work for you in LINQ to Entities? I get an error message: `The specified method 'System.String StringConvert(SystemNullable' 1[System.Double]' on the type 'Sysmte.DataObjects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities store expression.` – Guy Passy Sep 29 '13 at 07:08
0

Use the following LINQ expression:

            List<int[]> myList = new List<int[]>();
        myList.Add(new int[] { 2, 1 });
        myList.Add(new int[] { 3, 1 }); //Notice - same "PARENT"
        myList.Add(new int[] { 4, 1 }); //Notice - same "PARENT"
        myList.Add(new int[] { 3, 1 }); 

        List<int[]> myValues = new List<int[]>();
        myValues.Add(new int[] { 2, 1 , 1});
        myValues.Add(new int[] { 3, 1 , 2}); //Notice - same "PARENT"
        myValues.Add(new int[] { 4, 1 , 3}); //Notice - same "PARENT"
        myValues.Add(new int[] { 3, 1, 4 });
        myValues.Add(new int[] { 3, 2, 4 }); 

        var q2 = from value in myValues where myList.Select(x => new { ID = x.ElementAt(0), Parent = x.ElementAt(1) }).Contains(new { ID = value.ElementAt(0), Parent = value.ElementAt(1) }) select value;
        var list = q2.ToList();
chris huber
  • 51
  • 2