Retrieving entities with related tables c# using REST API2

Question

I am building a web API that is suppose to populate data from a linked child table using a where clause. I have attempted using include() with where() as per eager loading but without success.

public IQueryable<Market> getAllActive()
    {
        return db.Markets.Where(c => c.IsActive == true).Include(d => d.TravelCentres.Where(e => e.IsActive == true));

}

On researching, there are recommendations that I use explicit loading but it keeps error about the need to cast the data type. I am lost of ideas at the moment and will appreciate any help. Here is my code:

private TravelCentresDbContext db = new TravelCentresDbContext();    
public IQueryable<Market> getAllActive()
        {
            //return db.Markets.Where(c => c.IsActive == true).Include(d => d.TravelCentres);
            var result = db.Markets
                .Where(c => c.IsActive == true)
                .Select(p => new
                {
                    Market = p.MarketId,
                    TravelCentres = p.TravelCentres.Where(x => x.IsActive == true)
                });
            return (IQueryable<Market>)result;
        }

I get this exception message Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery1[<>f__AnonymousType42[System.String,System.Collections.Generic.IEnumerable1[TravelCentres.Models.TravelCentre]]]' to type 'System.Linq.IQueryable1[TravelCentres.Models.Market]'.

Blockquote

Paul Abbott · Accepted Answer · 2017-01-05 19:00:03Z

1

result is not an IQuerytable<Market>, it's an IQueryable of an anonymous type with properties Market and TravelCenters. So (IQueryable<Market>)result is an invalid cast. It would be advisable to create a model with Market and TravelCenters properties and then return that.

public class MyModel
{
    public int MarketId { get; set; }
    public IEnumerable<TravelCentre> TravelCentres { get; set; }
}

.

var result = db.Markets
            .Where(c => c.IsActive == true)
            .Select(p => new MyModel()
            {
                Market = p.MarketId,
                TravelCentres = p.TravelCentres.Where(x => x.IsActive == true)
            });

return (IQueryable<MyModel>)result;

answered Jan 5, 2017 at 19:00

Paul Abbott

7,2213 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Retrieving entities with related tables c# using REST API2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related