WHERE clause is Linq only?

[johnm]

I'm trying to populate a List with records that match a Collider[] array and it's just not working. I can successfully create a list with the whole table but not individual records.

This was my last failed attempt:

public class Test
{
   [PrimaryKey]
   public int testID { get; set; }
   public string name { get; set; }
   public float production { get; set; }
}

public List<Test> localList = new List<Test>();

public void Perimeter()
{
   Collider[] Colliders = Physics.OverlapSphere(transform.position, 100f);

   for (int i = 0; i < Colliders.Length; i++)
   {
       localList.Add (dbManager.Query<Natives>("SELECT * FROM table WHERE name = " + Colliders[i].name); // this line doesn't work
   }
}

[echo17]

String values need to be surrounded by single quotation marks, so something like:


localList.Add (dbManager.Query<Natives>("SELECT * FROM table WHERE name = '" + Colliders[i].name + "'");


Alternatively, you can use parameters that will add the quotation marks when necessary, like:


localList.Add (dbManager.Query<Natives>("SELECT * FROM table WHERE name = ?", Colliders[i].name);


Also, be sure the Natives class has the same structure as Test class. Actually, it seems to make more sense if you just use the Test class since that is what your localList is typed as:


localList.Add (dbManager.Query<Test>("SELECT * FROM table WHERE name = ?", Colliders[i].name);


[johnm]

'Natives' should be 'Test'. Sorry for the copy/paste typo.



I've changed the line to this:

localList.Add (dbManager.Query<Test>("SELECT * FROM table WHERE name = '" + Colliders[i].name + "'"));
and I get this error:
Argument 1: cannot convert from System.Collections.Generic.List<Test> to 'Test'.
I received this error before when trying to use 'Add' which is what lead me to use '=' to populate the List. Googling just leads to IEnumerable problems so I don't know where to turn from here.

[echo17]

Ah, right. Query returns a list of items, not just a single one. The Add method of List requires a single object. So there are a couple of ways you can do this:

1) Get the first element of the query. Note the [0] at the end that gets the first element of the query list:


localList.Add(dbManager.Query<Test>("SELECT * FROM table WHERE name = '" + Colliders[i].name + "'")[0]);


2) Use AddRange on your list to add all the elements of the query (whether it is one element or many):


localList.AddRange(dbManager.Query<Test>("SELECT * FROM table WHERE name = '" + Colliders[i].name + "'"));


I personally prefer the second method because it doesn't assume you have any elements in the resulting query. The first query will crash if there is no data when it tries to reference the first element of an empty list. Plus you can add groups of elements if you wish.

[johnm]

Using AddRange worked. Thanks for your help sorting this out.