Store table int ids into an int[] attribute of a ViewModel class

Question

I want to pull all int Ids from a joint table's column and have it stored in an attribute of a ViewModel class.

Note: Categorisation is a mapped table of Foo and Bar entity classes. I'm filtering only Ids of Foo in this case.

var model = _ctx.FooDbSet.Where(y => y.Id == x.Id).Select(x => new FooManageVM
        {
            // ... other attributes here

            CategorisationIds = _ctx.CategorisationDbSet.Where(w => w.Foo.Id == id).Select(s => s.Foo.Id).ToArray()

        }).Single();

Although there's the .ToArray() method at the end (.ToArray<int>() also doesn't work), I'm getting this error:

LINQ to Entities does not recognize the method 'Int32[] ToArray[Int32](System.Collections.Generic.IEnumerable`1[System.Int32])' method, and this method cannot be translated into a store expression.

I tried extracting the query to the above, like:

var ids = _ctx.CategorisationDbSet.Where(w => w.Foo.Id == id).Select(s => s.Foo.Id);

then below:

// this is of type int[]
CategorisationIds = ids.ToArray()

but that didn't work either.

Answer 1

I think the easiest thing to do is change the type of CategorisationsIds to IEnumerable<int>.

Also, check that you reference System.Linq

But if that doesn't work you could query an anonymous object and instantiate the FooManageVM with the result from .Single():

var result = _ctx.FooDbSet.Where(y => y.Id == x.Id).Select(x => 
    {
        // ... other attributes here

        CategorisationIds = _ctx.CategorisationDbSet.Where(w => w.Foo.Id == id).Select(s => s.Foo.Id)

    }).Single();

var model = new FooManageVM {
     // ... other attributes here
     CategorisationIds = result.CategorisationIds.ToArray();
}