Here's the scenario.

I use windows forms to add and update data. I don't like users editing inside a datagrid. After these operations (add, edit) are complete, the results are displayed grid-style, with View and Edit buttons in the grid column.

Problem is when this is new uncommitted data, with identity columns set to 0, I have no idea how to form a result set that spans more than one table.

example: I have a supertype entity Service and linked subtypes, Hotel, Flight, Car, etc, all of which are a 'type of' service. I have a subtype discriminator ServiceType. I want to display grid-style, e.g.,

Service Type Description, depart / arrival dates, times, etc.

So I need some construction which can link Service to ServiceType via an ID to display this for both saved and unsaved data.

Typed Lists are out because they are db dependent. Could this be done using a dynamic list.

In the case of uncommitted data, I don't want to save partial data, e..g, a Booking entity header record without a related Service entity, etc.

Any suggestions appreciated.

Typed lists aren't db dependent, they're based on entities.

It's not clear to me how you have your entities in memory. Do you have an entity collection in memory in which you've stored multiple service entity instances (of different types) and you now want to display for example only the 'hotel' types (filtered on servicetype) ?

I will have entities in memory for as long as a 'unit of work - in my case a booking header with any and all associated passengers, services and their subtypes, prices, client details, payment details.

Take the creation of a new booking. Once some basic business requirements have been met - a valid booking must have at least 1 passenger, one service, etc. - then the booking will be unsaved. ID columns will be 0.

My usual method of working is to show the lists of entities - services, for instance - in a grid, with grid options to view, edit, delete. To display the grid data in a user-friendly way I need to use reference data, e.g., I have a table called ServiceType, which provides the sub-type discriminator for the service subtypes, e.g., hotel, car, ferry, cruise, car, insurance, tour, transfer, etc.

Same goes for passengers: each passenger has a passengertype_id, which is provided by the reference table PassengerType. In the grid I need to show the text of the passenger type not the ID.

In a simple case, rather than displaying the ServiceType_ID in a grid I need to display it's type name. Same for passenger type.

I could I'm sure do this with saved data by using a typed list or a query. There are a few options.

But ow, when the data is new, do I construct a set of data items in such a way as to allow me to show this. Data would be read-only. You can see the sort of data I'm using in my previous post re. 1:1 entities.

Any suggestions again appreciated.

Your new Service can still have a related ServiceType entity that you could use to access the name and not the id of the ServiceType. So if someone edits a Service and they use a drop-down or whatever you need for your Service Type then you could show it using

ServiceEntity service = new ServiceEntity(); service.data = data; service.ServiceType = new ServiceType(ddlServiceType.SelectedValue);

then you are able to access service.ServiceType.Name for the name and not the id.

I've made some assumptions and probably oversimplified this, but maybe this will help get a solution started. If you could post a bit of code for how you create your new entities and then add it to a collection that is bound to the grid we'll be able to give a better solution.

At present, I'm using the entity as defined in the entity class, and this is what is being saved.

Take the case of a PassengerEntity. Fields Passenger_ID, int auto-increment Booking_ID, int PassengerType_ID, short Title, char Initials, char Surname, char IsLeadPassenger, bit

PassengerTypeEntity Fields PassengerType_ID PassengerType

So Passenger -> PassengrType is 1:n

What I need to display in the grid is the following:

PassengerType Title Initials Surname IsleadPassenger.

So for new data Passenger_ID, Booking_ID are 0. The grid will be read-only. I can obviously access the entity as stored, but need a different view of the data.

In sql terms of course it's a simple INNER JOIN

SELECT PassengerType.PassengerType, Passenger.Title, Passenger.Initials, Passenger.Surname, Passenger.IsLeadPassenger FROM Passenger INNER JOIN PassengerType_ID ON Passenger.PassengerType_ID = PassengerType.PassengerType_ID;

Hope this gives you a better idea of what I'm trying to do. I need this type of read-only view in quite a few places, so a bit of code, suggestion, etc. would move me on quit a bit.

One addition requirement: whatever data structure I use to bind to the grid, I need to add unbound columns and use tablestyles / columnstyles to add View, Edit, Delete buttons for each grid row. I will be doing this using something like RustemSoft's DataGridColumns .NET assembly.

Your help with this one appreciated.

I guess you need to add a TemplateColumn that contains a dropdown list that is boud to your collection of possible entities (like PassengerTypes).

And if you want to make the combo disabled, use Enabled = false property.

Template columns and grid formatting / presentation is not a problem. Whatever collection I use I should be able to add unbound columns to the grid.

Question is how to construct a collection based on the unsaved data which can provide me with a reference column from another table in place of an ID column from the base table.

Forget about presentation, tablestyles, dropdowns, just some idea, some sample code if possible to get me going on this. Have scanned the docs and there are plenty of examples of collection retrieval, filtering, etc. but in this instance the base table represents a collection object in memory.

How do I proceed.

These collections and entities will have a 1:n relationship. All you have to do is set the related entity property equal to the new entity. For instance here is a new department with new employees being added, which can be displayed or saved.

DepartmentEntity department = new Department();
department.Name = "Software Development";
EmployeeEntity employee1 = new EmployeeEntity();
EmployeeEntity employee2 = new EmployeeEntity();
employee1.Name = "Frans";
employee2.Name = "CliffH";
employee1.Department = dept;
employee2.Department = dept;

They are all new entities, but employee1.Department.Name will be "Software Development", and when you perform your save the new IDs will be generated and placed in the FK columns as well.

Sorry guys, I'm not seeing this, and I'm pretty sure it's my lack of familiarity with this tool.

// create a passenger entity
bm = new BookingManager();
passenger = bm.CreatePassenger();

// creates entity with columns: booking_id, passenger_id, passengertype_id, name, etc.

// get the list of passenger types to populate a combo box: passenger->passengertype is n:1

EntityCollection passengertypes = bm.GetPassengerTypes();
this.cbPassengerTypes.DataSource = passengertypes;
this.cbPassengerTypes.DisplayMember = "TypeOfPassenger";
this.cbPassengerTypes.ValueMember = PassengerTypeFieldIndex.PassengerType_ID.ToString();

// passenger entity created, add it to the booking entity
Booking.Passenger.Add( passenger)

// After adding, display the results in a grid. 
// bind passenger data to a grid.
gridPax.datasource = Booking.Passenger

This is what I see.

booking_id, passenger_id (these columns I can hide), passengertype_id, name, etc.

But I need this display to be:-

booking_id, passenger_id, passengertype.passengertype, name, etc.

1) Can i instantiate the passenger entity in such a way that I can match the passengertype_ID with the passengertype entity' passengertype_id and so retrieve the text of the passenger type instead of the id.

2) What are the implications when the entity is saved?

3) Would it not be better to construct a new entity collection for read only purposes which substitutes the fkey field passengertypeid with the description row from the passengertype entity.

4) I also want to add some unbound columns to the passenger collection because I want to insert command buttons for each row in the grid to implement Edit and Delete functionality. How can I expand a collection to include additional unbound columnsa ?

Appreciate that this post has 'dragged on' for a bit now. I'm sure I could write code that would 'fix' this issue, but I'm also fairly sure it wouldn't be necessary to do so.

Any further help appreciated.

Hate to push guys, but I desparately need some ideas for this problem.

If you could let me have a response asap that would be great.

CliffH wrote:

Sorry guys, I'm not seeing this, and I'm pretty sure it's my lack of familiarity with this tool...

1) Can i instantiate the passenger entity in such a way that I can match the passengertype_ID with the passengertype entity' passengertype_id and so retrieve the text of the passenger type instead of the id.

2) What are the implications when the entity is saved?

3) Would it not be better to construct a new entity collection for read only purposes which substitutes the fkey field passengertypeid with the description row from the passengertype entity.

4) I also want to add some unbound columns to the passenger collection because I want to insert command buttons for each row in the grid to implement Edit and Delete functionality. How can I expand a collection to include additional unbound columnsa ?

Appreciate that this post has 'dragged on' for a bit now. I'm sure I could write code that would 'fix' this issue, but I'm also fairly sure it wouldn't be necessary to do so.

Any further help appreciated.

I am late to this thread; but, I will throw in my 2-cents worth-- but, be careful with what I say (as always) because I am not a guru.

That said...

I will offer a quick suggestion by saying that one could probably address (1) and (3) and (4) by simply using custom-built TypedLists and/or TypedViews in LLBLGen.

We create a new TypedList in our application whenever we have to do something funky with the data and it works great.

That's about all I can offer; but, it may give you some ideas-- in short, if you haven't tried TypedLists, you should because they can make your life easier in a lot of situations.

IMHO.

Yours et cetera,

--Mark Kamoski

CliffH wrote:

Sorry guys, I'm not seeing this, and I'm pretty sure it's my lack of familiarity with this tool.

// create a passenger entity
bm = new BookingManager();
passenger = bm.CreatePassenger();

// creates entity with columns: booking_id, passenger_id, passengertype_id, name, etc.

// get the list of passenger types to populate a combo box: passenger->passengertype is n:1

EntityCollection passengertypes = bm.GetPassengerTypes();
this.cbPassengerTypes.DataSource = passengertypes;
this.cbPassengerTypes.DisplayMember = "TypeOfPassenger";
this.cbPassengerTypes.ValueMember = PassengerTypeFieldIndex.PassengerType_ID.ToString();

// passenger entity created, add it to the booking entity
Booking.Passenger.Add( passenger)

// After adding, display the results in a grid. 
// bind passenger data to a grid.
gridPax.datasource = Booking.Passenger

This is what I see.

booking_id, passenger_id (these columns I can hide), passengertype_id, name, etc.

But I need this display to be:-

booking_id, passenger_id, passengertype.passengertype, name, etc.

1) Can i instantiate the passenger entity in such a way that I can match the passengertype_ID with the passengertype entity' passengertype_id and so retrieve the text of the passenger type instead of the id.

Yes. You have to bind the Booking.Passenger also to the this.cbPassengerTypes combobox, using this.cbPassengerTypes.Databindings.Add(... ), where you bind the member passemgertype_id to the SelectedValue property of the combobox. This will then set the selected value in the combobox to the value in the entity for passengertype_id. Vice versa it then also works: selecting a new passengertype will set the passengertype_id.

It's kind of tricky though, consider this advanced databinding voodoo.

You can also use a Field mapped onto a related field, in the designer: Open Passenger in the entity editor and in the Fields Mapped onto related fields tab, you add a new mapping to passengertypes.Passengertype. This will then be a property in Passenger. It's key to have Passenger.PassengerType set to an entity of type PassengerType so there's a value returned when you bind Booking.Passenger to the grid. So after you've created passenger, you've to set passenger.PassengerType to an instance, not passengertype_id to a value.

2) What are the implications when the entity is saved?

None, this is all gui related.

3) Would it not be better to construct a new entity collection for read only purposes which substitutes the fkey field passengertypeid with the description row from the passengertype entity.

No, you don't need that. See above

4) I also want to add some unbound columns to the passenger collection because I want to insert command buttons for each row in the grid to implement Edit and Delete functionality. How can I expand a collection to include additional unbound columnsa ?

that's grid related. Use a grid which allows you to add unbound columns.

Thanks for this guys. I'll try the databinding solution first, failing that the designer revised entity approach.

if you can keep this post open for a while longer whilst I get this working I'd appreciate it.

Opted for the designer approach first, given known problems with databinding.

I added a 'fields mapped on related fields' field in the designer. So in the Passenger entity, in addition to the mapped database fields, I now have under 'Fields mapped on related tables':-

TypeOfPassenger (PassengerType.TypeOfPassenger) .

Made it read only as well.

BUT ... after re-generating, there is no reference at all to Passenger.TypeOfPassenger - not in the PassengerEntity class nor in ConstantsEnums.cs. Checked this several times to make sure.

Am I missing something?

You should have a property named TypeOfPassenger in the PassengerEntity, if you have added the field mapped onto related fields to the PassengerEntity in the designer. Please verify that the file is overwritten, it might be you didn't check out the file from sourcesafe or other sourcecontrol system so the file was readonly.

Re-generated again. Here is PassengerEntity.cs, dated 5 May 2006 16:21:51.

I'm in Delhi, so subtract 3.5 hours from this time.

///////////////////////////////////////////////////////////////
// This is generated code. If you modify this code, be aware
// of the fact that when you re-generate the code, your changes
// are lost. If you want to keep your changes, make this file read-only
// when you have finished your changes, however it is recommended that
// you inherit from this class to extend the functionality of this generated
// class or you modify / extend the templates used to generate this code.
//////////////////////////////////////////////////////////////
// Code is generated using LLBLGen Pro version: 1.0.2005.1
// Code is generated on: 25 May 2006 16:21:51
// Code is generated using templates: C# template set for SqlServer (1.0.2005.1)
// Templates vendor: Solutions Design.
// Templates version: 1.0.2005.1.111705
//////////////////////////////////////////////////////////////
using System;

//...

        /// <summary> Gets the value of the related field this.PassengerType.TypeOfPassenger.</summary>
        public virtual System.String TypeOfPassenger
        {
            get
            {
                PassengerTypeEntity relatedEntity = this.PassengerType;
                if(relatedEntity!=null)
                {
                    return relatedEntity.TypeOfPassenger;
                }
                else
                {
                    return (System.String)TypeDefaultValue.GetDefaultValue(typeof(System.String));
                }
            }

        } 
//...

(I'm examining the code, I chopped off the long post for now -- Otis)

I've chopped the code a bit, as it's not that useful to post all lines . I've left the property which you were looking for. It's right there, at line 1051

Sorry guys, this is getting a bit embarrasing for me. I'm not quite 'flying' with LLBLGen, but at least getting some lift off thanks to previous posts.

I still have issues with this 'field on related entity' scenario, and would be glad of a simple step-by-step solution or precise coding example, if poss. Using c# and Adapter, vs2003. Knowledge of databinding is rudimentary.

To recap:-

Datagrid needs to show Passenger.PassenterType.TypeOfPassenger instead of Passenger.Passenger_ID.

Data is added using a form where the collection PassengerType is bound to a combobox using:-

this.cbPassengerTypes.DataSource = passengertypes; this.cbPassengerTypes.DisplayMember = "TypeOfPassenger";. this.cbPassengerTypes.DisplayMember = "TypeOfPassenger"; this.cbPassengerTypes.ValueMember = PassengerTypeFieldIndex.PassengerType_ID.ToString();

This works fine for adding / storing data.

But once saved (not in the db as yet), the data is re-displayed in a read-only grid.

I would I think prefer a coding solution (databinding) than taking the designer route of adding entities on related fields and re-generating. I don't mind adding typed lists if this is an easier solution.

I've looked at prefetch paths and other stuff, but I'm afraid nothing that I've read is immediately obvious in this scenario.

Once again, any help appreciated.

Just to say as well ... and I'll take whatever advice a newbie can get - I have a fairly large number of cases like this. In one case I have

Service entity with 4 foreign keys FKs ServiceType_ID, Vendor_ID, Departure_Poing_ID, ArrivalPoint_ID

which needs to become ServiceType, VendorName, DeparturePoint, ArrivalPoint

fyi, departure and arrival points are 3-letter IATA city / airport codes, as in DEL - Delhi, LHR - London Heathrow, AMS - Amsterdam Schiphol. The list is fairly static, but IATA the governing body sometimes duplicate and amend, hence the need for an ID column.

Somebody make my day !!