I am generating templates and when I loop through foreign keys, I sometimes get duplicates.

For example, when running against the Northwind database, this foreign key is listed twice:

FK_Employees_Employees

NOTE: the key is part of the Employees table and its not actually duplicated in the datbase itself. There are three FKs, and I am getting four (with the duplicate).

I was using the latest ES 2007 with MyGeneration 1.3.0.3

Any ideas?

In order to make things easy during code generation we list all foreign keys a table is involved in. You can look at the PrimaryTable and ForeignTable properties to determine if this FK is on the table or on another table (involving this table).

---

EntitySpaces | Twitter | BLOG | Please honor our Software License

If I am understanding, if a foreign key is self-referencing (the primary table and foreign table are the same), the key will show up twice? I am not sure why, but okay.

I just created a list of foreign key names while looping through the foreign keys for a table... multiples/duplicates are ignored.

BTW: How can I post an ES template here? I created a template to loop through the entire database and create a very intellisense-friendly way to get access to database schema within VS. The result is very detailed and I would like to get some feedback.

Re: Duplicate foreign keys

This is not a bug in MyGeneration. It is by design. It occurs because of the bi-directional nature of relationships.

While you only add an FK constraint on the FK column in the db, it defines a relationship between that FK column and a PK column in that or some other table. Therefore, all relationships are bi-directional. FK_Orders_Employees represents both a zero-to-many from Employees to Orders, and a many-to-one from Orders to Employees. To account for both relationships, MyGeneration's MyMeta will list that same foreign key constraint on both tables. MyGeneration also allows you to access FKs at two levels:

1) All FKs for a table. It does not matter whether the constraint is part of that table. If the table is involved on either side, the FK will be listed. In our hierarchical template, we loop through the FKs at the table level:

foreach( EntitySpaces.MetadataEngine.IForeignKey fk in table.ForeignKeys )

2) All FKs for all columns within a table. Again, it does not matter if that column has the constraint defined on it in the db. If the column is involved on either side, the FK will be listed. So, a PK column is likely to have a number of FKs listed for it, because FK constraints on columns in other tables always reference PKs somewhere. If you loop through all columns for a table and grab all FKs for each column, then a self-referencing FK like FK_Employees_Employees will show up on the FK column (ReportsTo) and the PK column (EmployeeID). That will not happen if you loop through the FKs of a single table at the table level instead.

But, if you loop through all FKs for all tables (even at the table level), and just list out FK_constraint names, you will see every one of them twice, except for self-referencing FKs. Once for the Primary table, and again for the Foreign table.

As Mike mentioned, the way to tell which side of the FK you are dealing with, is to compare the Name property of the Table whose FKs you are looping through with the PrimaryTable and ForeignTable properties of each FK.

Re: Template Posting

Let me check with Mike and Scott. We have discussed allowing users to Blog about EntitySpaces, but I think we wanted to upgrade to Community Server 2008, first. The installer crashed on us the last time we tried it, and I believe we are still waiting for a patch from CS support. That would seem to be the best approach to allowing specific users to post attachments like templates. We may have to have you zip it up and send it to support@entityspaces.net. Then we could post it for you.

---

David Neal Parsons
www.entityspaces.net

I am looking at a simple concept: what foreign keys are associated to xyz table. When I get the FKs for a table, I sometimes got two - it could potentially be more. For me, multiples  only occur on self-referencing keys (from what I can tell) - I am looping FKs at the table level. No matter what, the keys are unique by name. So, I just wanted to list all FKs (no duplicates) for each table. It was simple enough to just keep track of FK names and not output anything (a duplicate) if that FK name had already been output for that table...

I see your view and there is nothing wrong (no bug) - I agree. For my purposes, there is no reason to repeat FKs because I just want the table's 'outward' view; both directions has no added benefit.

I'll send the template tomorrow: its a small piece of a bigger puzzle, but I would like to share it and get feedback. For example, I am outputting nested static classes and I discovered the names of members are sometimes matching names derived from the database. So, I ran into a few quirks where a parent name (data/info on the database such as a column name) matched my class name and caused 'bad code'. I fixed the problem by modifying some members names by adding prefixes.

 Anyway, if you run it, I think you will get why I think 'its cool'.

 

 

Hi Trevor

Looks like you've achieved what you wanted to (by eliminating dupes in code?) but have you looked at the code in the old "Foreign Key" sample template as that listed out all the FK's (and more info about them) for each table and I can't see any duplication going on in the output there at all (If you need it I've converted the template to use the newer EntitySpaces metadata engine) - might be worth looking at the code in that one to see if it helps you

Cheers

Martin

I started with that code. The issue arises because of the details I require and the difference in how I needed/wanted to output the results. Everything is okay.

David, did you get the email with the template I sent to you here:

support@entityspaces.net

I don't see it in my history of email and am wondering:

1) You didn't get it and I simply need to resend. (I goofed somehow)

2) You didn't get it because I forgot to zip the attachment (may have been blocked).

3) You did get it okay.

Im not panicked and don't expect immediate response, but I would like to know if I need to resend.

Thanks

We did indeed get it. Thx.

We have not had time much time to spend with it, yet, but your screen-shots have us drooling in anticipation.

---

David Neal Parsons
www.entityspaces.net

Hurry up and look at it David - you know comments like "....have us drooling in anticipation" are going to make me eager for a look too!

:D

Martin

Okay, I got some very good feedback from David and I believe I addressed what he discovered; I then resent another version.

 Hopefully, they will have a way to post the template because I am eager for other users to try it and tell me what they think. I expect for potential issues to arise (hoping not - I'm sure everyone understands the potential for undiscovered bugs). I do think it will work for most users as it is now, but - hey - there have been no tests beyond myself and a brief test by David.

Here is a teaser:

 

 

 

NOTICE: The intellisense picks up descriptions of the schema AND lists the associated value (in the last example, the IsManyToOne for 'FK_Employees_Employees' is set to 'true' for the 'Employees' table. I think this is GREAT discoverability of datbase schema within VS and I fully intend for the values to be compatible with the GUI Query Builder I am working on.

Create a folder under your MyGeneration Templates folder and un-zip the template into it. You should be able to find the template under the "DatabaseSchema" namespace in the MyGeneration Template Browser. It is an ES2007 style template, so you'll need esPlugin.dll in your MyGeneration Program Folder. The generated class stands completely on its own, so you should be able to include the file in either ES2007 or ES2008 projects.

Thx, Trevor

EDIT: 2008-05-28 - See below for the latest versions.

---

David Neal Parsons
www.entityspaces.net

Thanks for posting the template... hopefully, some users will give it a try and let me know what they discover.

Not a problem. I took the liberty of editing the title of your original post. I think it will attract more readers

---

David Neal Parsons
www.entityspaces.net

Hi all

Just downloaded and ran this and although I don't have time to play with it at the moment I wanted to say firstly - great job, I'm positive this is going to be very useful, certainly for me and I'm sure many others so thanks for taking the time to develop and share it!

Now, some initial feedback - most of which is cosmetic but still important (from an EntitySpaces team point of view I believe)

1) If the DB name has spaces in then the class generated includes those spaces and therefore won't compile - easy enough to remove them but thought it worth a mention

2) The class summary comments - it currently shows something along the lines of "/// The 123Whatever table" which is the cleaned up version of the table name rather than "the 123 What ever table" - I'd just use the "source" name here rather than running it through the esPlugin.Entity() method.

The following are more about branding etc for ES so aren't issues as such but I'd change them to save any confusion

a) The EntitySpaces header and copyright info is still included - the ES header comes out on the generated file as well (you can change this in the "Interface code" tab)

b) When you run the template, the titlebar of the input window shows the ES copyright and the header text says "EntitySpaces Light Weight Proxy Stub" - the footer includes the ES copyright as well (you can change this in the interface code tab too)

c) The default Namespace is "Client" whereas the usual default is "BusinessObjects" - just makes it more consistent.

As I say, these are pretty much all cosmetic (with the exception of the first one) but I know Mike has said in the past about making sure any code not generated by their own templates needs to make sure it doesn't appear as if it is from them (so they don't get asked to support it, or so it doesn't impact on branding etc).

Anyway, I'll have a play with this as soon as I get a chance but thanks again Trevor - looks like it's going to be a great addition to the toolbox!

Cheers

Martin