CODEWITHSUNDEEP
c#entity-frameworklinq
user3692334
user3692334

Reputation: 45

Return type is incorrect, but I don't understand what it should be

I'm trying to redo this method to only return the appropriate records. The original GetAll() below returns a lot of unnecessary columns and data because EF proxies are being used. Turning the EF proxies off breaks stuff in the app, so I'm trying to selectively reduce data returned to improve performance.

Original GetAll()

    public IEnumerable<OWF_ManagerRelationship> GetAll()
    {
    return context.OWF_ManagerRelationship.Where(x => x.IsActive == true).ToList();
    }

The new GetAll() I'm trying to create is below. This one will have some fields from the OWF_Onsites table in the commented out Include() statement. These will be a couple date fields and one integer field. This is where I'm attempting to reduce the amount of data returned from this method to the calling one. I want to return the one DisplayName field from the Owf_ManagerRelationship context and the other 3 (OnsiteDate, TargetDate, TargetMonth) from the OWF_Onsites table.

    public IEnumerable<string> GetAll()
    {
        var managers = context.OWF_ManagerRelationship
            .Where(m => m.IsActive.Value == true)
            //.Include(o => o.OWF_Onsites).Where()
            .Select(x => new {x.DisplayName})
            .ToList();

        return managers;         
    }

The return managers above has red squiggles. It says "Cannot convert expression type System.Collections.Generic.List<DisplayName:string> to return type System.Collections.Generic.IEnumerable<string>".

I understand that it can't convert the anonymous type string DisplayName to return type string, but I don't really understand why. I thought it would work because the DisplayName is a string. I also don't understand what the return type should be once I get the code in the Include() statement working. It should also return the two date fields and the one integer field from OWF_Onsites once this has been done.

Can some one please shine some light on what this should look like? Including some code and how to use the two date fields(targetDate and OnsiteDate) and integer(TargetMonth) field from the OWF_Onsites table in the Include() would be really helpful.

Hopefully, my intent here is clear in case my attempt to do it is way off base. Please let me know if I'm doing this all wrong.

Upvotes: 3

Views: 130

Answers (2)

Keith Barrows
Keith Barrows

Reputation: 25308

You should look at IQueryable projections to do what you are trying to do. Here are a few great blog posts on getting started:

By using these concepts I am able to write a generic repository method like this:

public IEnumerable<T> GetAllProjectTo<T>(params Expression<Func<TEntity, bool>>[] filters)
{
    var query = Table.AsQueryable();
    if (filters != null)
    {
        query = filters.Aggregate(query, (current, where) => current.Where(where));
    }

    return query.Project().To<T>().AsEnumerable();
}

What this means is if I have this data definition (EF):

public class FullModel
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public int QuantityOnHand { get; set; }
    public double UnitCost { get; set; }
    public DateTime Created { get; set; }
    public string CreatedBy { get; set; }
    public DateTime Updated { get; set; }
    public string UpdatedBy { get; set; }
}

And I want only this representation from the data:

public class ShortModel
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int QuantityOnHand { get; set; }
    public double UnitCost { get; set; }
}

I can now do this:

IEnumerable<ShortModel> results = _repository.GetAllProjectTo<ShortModel>(a => a.Title.Contains("milk"));

The secret sauce in the above is the definition of Project() and To(). (Read through the sample in the 2nd blog I posted.) What these two methods do, in tandem, is affect the SQL being sent to SQL Server. In the code sample you have above, even with the Select, it will issue a SELECT * FROM command then apply the SELECT during materialization. This means, all the data in all the affected rows is being brought back to you first!

In your case, it might look like:

public class MyViewModel
{
    // Name of field MUST match name of field in EF/ORM...
    public string DisplayName { get; set; }
}
public IEnumerable<string> GetAllManagers()
{
    var displayList = context.OWF_ManagerRelationship
        .Where(m => m.IsActive.Value == true)
        .Project()
        .To<MyViewModel>()
        .ToList();

    IEnumerable<string> managers = displayList.Select(a => a.DisplayName).ToList();

    return managers;
}

Upvotes: 2

Alexander Derck
Alexander Derck

Reputation: 14488

You are trying to return an IEnumerable of an anonymous type instead of IEnumerable<string>. Remove the new in the select and it will work:

public IEnumerable<string> GetAll()
{
    var managers = context.OWF_ManagerRelationship
        .Where(m => m.IsActive.Value == true)
        .Select(x => x.DisplayName)
        .ToList();

    return managers;         
}

Some other things of note: You can also simplify your code by omitting .ToList() since .Select will already return and IEnumerable<string>. Note you will still have to enumerate the query later on (using ToList for example) to get the results because of deferred execution.

public IEnumerable<string> GetAll()
{
    var managers = context.OWF_ManagerRelationship
        .Where(m => m.IsActive.Value == true)
        .Select(x => x.DisplayName);

    return managers;         
}

Upvotes: 5

Related Questions