Implementing SQL's "not exists" in Silverlight RIA services

Question

I'm trying to use Silverlight with RIA services. CRUD operations are pretty easy. But I am stuck with the "not exists" case.

I have two entities Person (personID, personName) and Car(carID, personID). Some person have a car, but only one car.

 internal sealed class Person
        {
            private PersonMetadata() { }

            public int personID { get; set; }

            public string personName { get; set; }

            [Include]
            public Car CarNavigation  { get; set; }

        }


 internal sealed class Car
        {
            private CarMetadata() { }

            public string carName { get; set; }

            public int personID { get; set; }

        }

    public IQueryable<Person> GetPersonEager()
    {
        return this.ObjectContext.Person.Include("CarNavigation");
    }

<sdk:DataGrid Name="PersonGrid" AutoGenerateColumns="False">
                    <sdk:DataGrid.Columns >
                        <sdk:DataGridTextColumn Binding="{Binding personID}"  />
                        <sdk:DataGridTextColumn Binding="{Binding personName}"  />
                        <sdk:DataGridTextColumn Binding="{Binding CarNavigation.carName}"  />
                    </sdk:DataGrid.Columns>
                </sdk:DataGrid>


PersonGrid.ItemsSource = context.Person;
context.Load(context.GetPersonEager());

This structure works pretty well, for update. I also can update the car's name.

But, I also want to list the persons who does not have a car. And I also want to be able to add cars to them. I have tried:

 public IQueryable<Person> GetPersonEagerWhoDoesNotHaveACar()
        {
            return this.ObjectContext.Person.Include("CarNavigation").Where( x => x.CarNavigation == null );
        }

The method above returns me the persons without cars, but the Car entity is empty. How sould I modify this to make it return the persons without cars and the car entity's key is filled.

I'm not sure if I understand your question. Do you want it to return an empty Car entity? — Danexxtone
I want to return a Car entity with its "personID" field is filled and carName is empty. So I can fill it. Since I make a left join while finding the persons without car, I know their (the cars') personID field. So entity framework should know it, too. But the GetPersonEagerWhoDoesNotHaveACar() method returns the persons without car, but I expect the Person.CarNavigation.personID field is filled. Maybe my query is wrong, or maybe EF does not support it or maybe I should do some customizations or maybe I should develop another approach ;) — fkucuk

Anatolii Gabuza Anatolii Gabuza · Accepted Answer · 2011-12-27T20:04:49

If "Some person have a car, but only one car" - then you've got wrong DB architecture. Why do not to place nullable carId into Person? In such case you'll easily include and manipulate(add, delete etc.) everything you want with or without related Car.

Note: if you steel need such approach with current architecture - then clarify what should return query(list of Persons with Car set to null for not connected items) and for what purpose.

Implementing SQL's "not exists" in Silverlight RIA services

1 Answers