Entity Framework Help

[paulthepaddy]

Didn't Know how To Title This, Sorry For Generic Title.

Hi all, i am trying to do something in theory seems quite simple to me but i just cant get my head around it.

I am using the Entity Framework Database First Model, and i am trying to create an invoice from a previously made order.

I am using alot of binding sources to do what i am trying to do and i think im doing it' the proper way'
but first off the yellow box simply retrieves a list of cars depending on the customer and the department like so, i had wanted to use a checkedListBox instead but il enquire about that after i can get the main functionality of the form working.

Me.listofcars = From Car In db.Cars Where Car.Dealership_ID = Invoice.Dealership_ID And Car.FullyPaid = False AndAlso Car.Orders.Any(Function(ord As Order) ord.Department_ID = Invoice.Department_ID) Select Car
        CarBindingSource.DataSource = listofcars

        Me.lbCars.DataSource = CarBindingSource
        Me.lbCars.DisplayMember = "Reg"

And the result is then binded the Red(left) hand box, theirs a few textboxes with info but my main problem is the Blue box. i am using a BindingSource to bind the Listboxs item and the orders

 Private Sub lb_Cars_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lbCars.SelectedIndexChanged
        Dim car As Car = CType(Me.lbCars.SelectedItem, Car)
        OrdersBindingSource.DataSource = car.Orders

The Orders Datagridview (Blue) i have added a checkbox column for the user to select the orders to go onto the invoice, but i am struggling to get the orders added to the invoice

as far as i can work out i can either go like so Me.Invoice.Cars.Add(car) (this will add all order numbers related to the car even if i have removed the unwanted orders from the car.orders
or i can go Me.Invoice.Orders.Add(ord) but entity framework isn't filling in the car information.

if you guys really want i can post code, but i am more curious how you would approach this as this would be very similar to a normal sales application.

dont get me wrong im not looking for someone to code this form me but more the logic behind it, eg bind this to this loop through this and so on.
Thanks

 

[jmcilhinney]

Can you show us the EF diagram?

 

[paulthepaddy]

oww of course sorry, didn't think about that but obviously it is needed

 

[jmcilhinney]

I'm a little bit confused as to why there's a direct relationship between Car and Invoice. If a Car is related to an Order and an Order is related to an Invoice then surely you're duplicating data to then relate a Car to an Invoice. It would generally be considered better to access the Car records related to an Invoice via an Order. Is there any reason that you can't do that? Is it possible for a Car to be related to an Invoice without their both being related to the same Order?

 

[paulthepaddy]

Hi jmc thanks for looking at that, nope your 100% correct their is no reason for the car to be directly related to the invoice, if im honest i just didn't think of getting the car info via the order and that also helps as i wouldn't need the cars to have a dealership id, occasionally a car we have worked on will show up a while after in another dealership, i had thought about the issues that could be caused if a car can only be related to 1 dealership at one time. so thank you for that, and that also solves my problem of all orders related to a car being added to an invoice when i'm only wanting 2 or 3 orders added.

and actually the more im looking at it the more im thinking i think i can get rid of a few Dealer_ID FK, if the objects(inv,ord) are related to a department and that department is related to an order, it would also make more sense to get the dealer info from the departments. looks like you have started me looking and thinking a bit more about the model. thanks

 

[paulthepaddy]

Can i also ask while we are talking about this Form. At the moment i am having to have the user select an item from the (Red)listbox then loop through the (Blue)datagrid to check for orders i want to add to the invoice.

would it be easy to change the ListBox for a CheckedListBox, But i need the (Blue)DataGridView to remember what orders where checked in the first column, but my other problem is when the the loop finishes the first loop the DataGridView wont change to display the orders from the car being looped though, could i ask how you would do this?

Thanks again