Filtering entity with another entity

Question

Im trying to filter an entity with another entity with this approach:

         [HttpGet]
    public async Task<IActionResult> GetViewIO()
    {
        int uid = (int)HttpContext.Session.GetInt32("userId").Value;

        if(uid == 2)
        {
            uid = 33;
        }

        var listAeGrp = _context.ListofAE.FromSql($"Execute ListOfAe {uid}");
        var _orders = _context.OrderHeaderView.FromSql(@"OrderHeaderView").OrderByDescending(f => f.id).OrderBy(p => p.platform_id);

        var orders = await _orders.Where(x => listAeGrp.Any(y => y.id == x.created_by)).ToListAsync();
        
        return Json(orders);
    }

So basically, Im getting the values of listAeGrp and _orders using FromSQL and a stored procedure in MSSQL. listAeGrp relies on the current user that is logged in while _orders has a plain select of data created.

With this separate variable I can check that they have values but when im doing the filtering using .Any of linq, an error is returned:

Could not parse expression 'value(Microsoft.Data.Entity.Query.Internal.EntityQueryable`1[DigitalMVC.API.Models.listofAE]).FromSql("Execute ListOfAe 33", value(System.Object[]))': This overload of the method 'Microsoft.Data.Entity.RelationalQueryableExtensions.FromSql' is currently not supported.

Im currently using VS2015, dnx4.5.1

So my questions is:

1. Where did I go wrong? Im fairly new to MVC and Linq.
2. Is there other way to do this?

Thank you so much for the help!

Answer 1

What I did is instead of using an Entity to filter another entity, I created a function that will return a list of int

public List<int>ListOfUsers(int uid)
    {

        List<int> listOfAeGroup = new List<int>();
        var listAeGrp = _context.ListofAE.FromSql($"Execute ListOfAe {uid}");
        listOfAeGroup = listAeGrp.Select(x => x.id).ToList();
        return listOfAeGroup;

    }

Then I used the returned function and use it this way:

List<int> allowedUserId = ListOfUsers(uid);    
var orders = await _context.OrderHeaderView.FromSql(@"OrderHeaderView").Where(x => allowedUserId.Contains(x.created_by)).OrderByDescending(f => f.id).OrderBy(p => p.platform_id).ToListAsync();
            return Json(orders);