How Select From Parent Based on Child Property in EntityFramework and ESQL

Question

I have a parent/child table in Entity Framework and I need to select some row from parent based on child primary key.

I write these code:

List<int> s = (from all in DB.TbProfiles
                    where all.TbMaharat.Any(c => maharat.Contains(c.MaharatId))
                    select all.ProfileId).ToList();

but I Found that Linq2Entity does not support "Contains", and I must use "MultiSet" and ESQL command. but i can find any sample to do this.

I write ESQL like this but it does not work:

byte[] moshTypes = new byte[] { 1, 2, 3 };

        List<int> s =  DB.TbProfiles.Where("it.TbMaharat exists(Select 0 from TbMaharat as e where e.MaharatId IN MultiSet (" + string.Join(",", moshTypes) + "))")
            .Select(c=>c.ProfileId).ToList();

        return s;

Can anyone help me?

Answer 1

To use IEnumerable<T>.Contains() method you'd have to load the table into memory using ToList() or ToArray(). However this can lead to great memory cost so just don't do it unless the table is very small and won't grow in time. There is a workaround for Contains method: build an OR expression for each value so the Contains() method becomes DB.TbMaharat.Where(x=>x.MaharatId == 1 || x.MaharatId == 2 || x.MaharatId == 3). See the MSDN thread on how to do this: http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/ .