How to use LINQ Where for generic type?

Question

I have the following generic method that I need to be able to perform a LINQ Where query in:

public static List<T> GetItems<T>(Guid parentId = new Guid()) where T : new()
{
    var db = new SQLiteConnection(_dbPath);
    List<T> result;

    if (parentId != Guid.Empty)
    {
        result = db.Table<T>().Where(i => i.ParentId.Equals(parentId)).ToList();
    }
    else
    {
        result = db.Table<T>().ToList();
    }

    db.Close();
    return result;
}

The compiler doesn't like the following line

result = db.Table<T>().Where(i => i.ParentId.Equals(parentId)).ToList();

error: cannot resolve 'ParentId'

Is it possible to use generics in this way in a LINQ query? Note that object of type T will always have a ParentId property.

Answer 1

You used a generic type and the compiler don't know which entity are you going to use.

Just use reflection feature of .NET language.

result = db.Table<T>().Where(i => i.GetType().GetProperty("ParentId").GetValue(src, null).Equals(parentId)).ToList();

Answer 2

You should concretize T parameter with some interface which will include required values. Also, you should add this interface to all types that contains this field or base type for its classes.

public interface IHierarchy
{
    public Guid ParentId { get; }
}

public static List<T> GetItems<T>(Guid parentId = new Guid()) 
    where T : IHierarchy, new()
{
    var db = new SQLiteConnection(_dbPath);
    List<T> result;

    if (parentId != Guid.Empty)
    {
        result = db.Table<T>().Where(i => i.ParentId.Equals(parentId)).ToList();
    }
    else
    {
        result = db.Table<T>().ToList();
    }

    db.Close();
    return result;
}

If you have 2 types of entities and the first contains required values and the second does not, then you can have two overloads for this scenario.

Answer 3

The problem is that in your code you assume that every T has a GUID property ParentId, while in fact you only required that every T has a default constructor. You need to require that every T has a ParentId.

You could do this by requiring that every T implements some interface. Like other answers suggest, however this is quite a nuisance, because for every class that you want to use this function for you'll need to implement this interface.

The function Enumerable.Where seems to be able to do the same job, without requiring any interface from the input items. So let's use the same method:

As input we tell which property to use (in your case ParentId) and with which value to compare (in your case parentId).

The only requirement we have is that we must be able to compare ParentId with parentId: it should be IEquatable

public List<T> GetItems<T, TKey>(Func<T, TKey> keySelector, Tkey value)
    TKey : IEquatable<TKey>,
{
    ...
     result = db.Table<T>()
       .Where(t => keySelector(t).Equals(value))
       .ToList();
}

Usage:

    Guid value = ...
    var items = GetItems<MyClass, Guid>(item => item.ParentId, value);

This function will also work with other classes and other properties:

int studentId = ...
var students = GetItems<Student, int>(student => student.Id, studentId);
var otherStudents = GetItems<Student, string>(student => student.Name, "John");

Two side remarks: - you use new Guid() to define some default Guid. It is faster to use Guid.Empty - You will not create new items of type T. They are already in your dbContext.Table. Therefore you don't need new(). - However, if your Table requires that T is a class then you should require that. See your Table definition:

where T : class