DbSet.FirstOrDefault()?

Question

I'm trying to do this but it says I can't use FirstOrDefault,

public static int GetId(this Context db, Type type, string name)
{
    return db.Set(type).FirstOrDefault(x => x.Name == name).Id;
}

The error is 'System.Data.Entity.DbSet' does not contain a definition for 'FirstOrDefault' and no extension method 'FirstOrDefault' accepting a first argument of type 'System.Data.Entity.DbSet' could be found (are you missing a using directive or an assembly reference?)

I then tried this Cast method but that gave an error Cannot create a DbSet from a non-generic DbSet for objects of type 'WindowStyle' (btw WindowStyle inherits from DomainEntity below),

var set = db.Set(type).Cast<DomainEntity>();
return set.FirstOrDefault(x => x.Name == name).Id;

Here is the class,

public class DomainEntity
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

Solution 1

This answer possibly doesn't help you depending on how "dynamic" the situation is where you call this method, basically if you know the type at compile time or not. If you know it you can write a generic method instead:

public static class MyExtensions
{
    public static int? GetId<TEntity>(this Context db, string name)
        where TEntity : DomainEntity
    {
        return db.Set<TEntity>()
            .Where(x => x.Name == name)
            .Select(x => (int?)x.Id)
            .FirstOrDefault();
    }
}

I've changed it to a projection because you don't need to load the full entity if you only want the Id. You can let the database do the work to select the property to improve performance a bit. Also I return a nullable int in case that there is no match for the name in the database.

You can call this in your code like so:

int? id = db.GetId<WindowStyle>("abc");

As you can see for this solution you must specify the type WindowStyle at compile time.

This assumes that DomainEntity is not part of your model (there is no DbSet<DomainEntity>), but just a base class of your entities. Otherwise @Paul Keister's solution would be easier.

Edit

Alternatively you can also try the following:

public static class MyExtensions
{
    public static int? GetId(this Context db, Type entityType, string name)
    {
        return ((IQueryable<DomainEntity>)db.Set(entityType))
            .Where(x => x.Name == name)
            .Select(x => (int?)x.Id)
            .FirstOrDefault();
    }
}

And call it:

int? id = db.GetId("abc", someType);

It will throw an exception though at runtime if someType does not inherit from DomainEntity. The generic version will check this at compile time. So, if you can prefer the first version.

Solution 2

Maybe you are missing

using System.Linq;

Solution 3

The first construct won't work because you are working with a non-generic DbSet, so you can't apply the FirstOrDefault extension method, which only works with a generic. It sounds like you understand that already, because you're already trying to get the non-generic DbSet. The error you're getting with the Cast() method is caused by your attempt to cast a DbSet to a DbSet. This can't be allowed, because if it were it would be possible to add non-conforming members to the DbSet (objects of type other than WindowsStyle). Another way of saying this is that Covariance is not supported for DbSets because DbSets allow additions.

I think you'll have to find another way to do what you're trying to do. Mixing LINQ and inheritance this way obviously problematic. Since you have a base type defined, and you're only working with attributes that are available on the base type, why not just query the base type?

    public static int GetId(this Context db, string name)
    {
        return db.DomainEntities.FirstOrDefault(x => x.Name == name).Id;
    }

You're probably worried about name collisions between the various types, but you can probably start with this and look at the derived type associations to verify that you're looking at the correct type. One way to deal with this is to add a type flag to your DomainEntity definition.

Solution 4

Here's the issue. The DbSet class has its own implementation of Cast<T>() that ONLY allows database types (such as Cast<WindowStyle>()), so this method does not allow Cast<DomainEntity>() and is throwing the exception.

Instead, you want to use the IQueryable.Cast<T>() extension method, which will simply cast your data to the base type. Here's an example:

var set = ((IQueryable)db.Set(type)).Cast<DomainEntity>();
return set.First(x => x.Name == name).Id;

Solution 5

Here is an idea I had that seems to be working.

public static int GetId(this Context db, Type type, string name)
{
    var set = db.Set(type);
    foreach (dynamic entry in set)
        if (entry.Name == name)
            return entry.Id; 
}

View more solutions

Comments

[Adam Tuliper]

and what happens here if your object is null and you try to reference id : )

[Benjamin]

Paul, Thanks. But I don't have a DbSet<DomainEntity> on the DbContext. Is that why I'm having a problem? I was just using DomainEntity to organize other classes by inheritance. I think the real problem is I don't understand generics.

[Benjamin]

I think I tried Where but it had the same problem as FirstOrDefault. Thanks.

[Benjamin]

I tried using MakeGenericType but it said DbSet<DomainEntity> is not a generic type.

[Paul Keister]

The DbSet will retrieve entities from the database. So if you don't have a separate database table for the DomainEntity entity, this approach will fail. Using DB first, this can definitely be done. It sounds like you're using code first; I've never done this with code first but it should be possible. In any case, you will need a DomainEntity DbSet. On the database side, inheritance can be modeled by making the primary key for derived entities also a foreign key to the id of the parent entity.

[Scott Rippey]

This does fix a problem (improper use of FirstOrDefault), but it's unrelated to the question.

[Scott Rippey]

You should be able to replace dynamic with DomainEntity here, which will give you nearly the same as my answer.

[Slauma]

Loading a full table into memory (perhaps 1 million rows) to get just a single Id looks very uncool. The start of your loop will execute the query set with no filter at all. Stay away from this solution.

[Zoka]

If I could give you all my reputation here, I would - this is what I'm looking for about an half year and I always did wrong approach. Thx, you are really my god now.

[Shimmy Weitzhandler]

@Slauma, besides, he returns the match after iterating the entire table :) post edited.

[Dave Lucre]

I forgot to make sure that System.Linq was in the usings! It's almost always there by default, and I never thought to check for it.