To continue the closed thread...

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2893

How do you determine what column is the identity column for an entity mapped on a table? (table meta-data I assume) If LLBLGen operates on a setting in the project file; then would it work to allow the user to choose what column in the view was the identity column? The UI would be much like the "Is readonly" flag you added.

Solving this last issue of LLBLgen not being able to refetch an entity mapped onto a view is the last hurdle (I hope) to implementing my architecture.

Thanks CT

ctadlock wrote:

To continue the closed thread...

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2893

How do you determine what column is the identity column for an entity mapped on a table? (table meta-data I assume) If LLBLGen operates on a setting in the project file; then would it work to allow the user to choose what column in the view was the identity column? The UI would be much like the "Is readonly" flag you added.

Solving this last issue of LLBLgen not being able to refetch an entity mapped onto a view is the last hurdle (I hope) to implementing my architecture.

Views don't have an identity column, I can't determine that information as well as it's not stored in the DB. I could enable the IsIdentity checkbox, but that wouldn't do anything at runtime, as the Identity flag isn't present on the fields, so the DB won't insert the identity field, i.e.: it depends on your way of inserting data for the view, which could mean anything. For example if you opt for a trigger, likely the scope in which the insert takes place, is not the scope in which SCOPE_INDENTITY() works on, i.e. the insert statement executed. (which triggers the trigger).

So you use SCOPE_IDENTITY() to get the value; makes sense. You're correct, a table/view with an insert trigger won't pick up the value, but @@identity will. I realize there can be possible side effects of using @@identity; but for this specific case, those side effects are exactly what I'm looking for. Is there any way to set a flag on an entity to have it choose to use SCOPE_IDENTITY() or @@identity?

The other issue with views that could come up is that you must pass a value for the identity column when you insert into it; even though the value is actually set by the system. Actually, this is true for any non-null column. How would LLBLGen handle that?

Let me explain why I'm trying to use views in the first place. I want to provide a small amount of abstraction between my physcial layer (database) and my logical layer (entities). In any database there are many "static" tables that contain readonly data, which are related to other R/W tables.

// R/W table
ACCOUNT
AccountID
Username
AccountTypeID

// Read only table
ACCOUNTTYPE
AccountTypeID
Name

I would like to map an entity on a view of the r/w table which included some data from the readonly table.

ACCOUNTVIEW
AccountID
Username
AccountTypeID
AccountTypeName

I will then use INSTEAD OF triggers to handle the crud operations. This gives my entites a consistant interface to what an Account is (through the AccountView).

I'm trying to use this approach instead of mapping an entity to every table in my database because my entities must be very efficient over the wire (remoting). Adding an additional entity to the entity graph increases its size. Also is doesn't make sense to provide R/W capability to tables that are read only.

My backup plan is to use the "field mapped on related entity" feature (which is great) and "hide" the read only entity. This will fix my entity "api" issues, but doesn't help the wire weight issue.

If someone knows a better solution than what I've got here, I'm all ears.

Thanks again for the help. CT

ctadlock wrote:

So you use SCOPE_IDENTITY() to get the value; makes sense. You're correct, a table/view with an insert trigger won't pick up the value, but @@identity will. I realize there can be possible side effects of using @@identity; but for this specific case, those side effects are exactly what I'm looking for. Is there any way to set a flag on an entity to have it choose to use SCOPE_IDENTITY() or @@identity?

At the moment you have to replace the SCOPE_IDENTITY() strings to @@IDENTITY in the entityfieldfactory (selfservicing) or persistenceinfofactory (adapter)

Though don't underestimate the side effects. I can't guarantee it's threadsafe.

I also think it won't work, because you cant define teh field as an identity field, thus the sequences aren't there, so the runtime code won't read the value back.

The other issue with views that could come up is that you must pass a value for the identity column when you insert into it; even though the value is actually set by the system. Actually, this is true for any non-null column. How would LLBLGen handle that?

The problem you will be facing is that the value won't be read back as the field isn't set to be an identity field. Thus you have to provide a value for it.

Let me explain why I'm trying to use views in the first place. I want to provide a small amount of abstraction between my physcial layer (database) and my logical layer (entities). In any database there are many "static" tables that contain readonly data, which are related to other R/W tables.

// R/W table
ACCOUNT
AccountID
Username
AccountTypeID

// Read only table
ACCOUNTTYPE
AccountTypeID
Name

I would like to map an entity on a view of the r/w table which included some data from the readonly table.

ACCOUNTVIEW
AccountID
Username
AccountTypeID
AccountTypeName

I will then use INSTEAD OF triggers to handle the crud operations. This gives my entites a consistant interface to what an Account is (through the AccountView).

I'm trying to use this approach instead of mapping an entity to every table in my database because my entities must be very efficient over the wire (remoting). Adding an additional entity to the entity graph increases its size. Also is doesn't make sense to provide R/W capability to tables that are read only.

My backup plan is to use the "field mapped on related entity" feature (which is great) and "hide" the read only entity. This will fix my entity "api" issues, but doesn't help the wire weight issue.

If someone knows a better solution than what I've got here, I'm all ears. Thanks again for the help. CT

Use GUID's for the keys. I think that solves the problem. Or another form of key.

Be aware that if you have a field mapped onto a field in a related entity, you can't hide the relation between the two.

I'm not sure how GUIDs would help my situation.

I tried changing the "Sequence Name" value of the identity fields to @@identity and setting the IsIdentity flag to true. This almost worked; now a SQL issue is stopping me...

I have a view that has an INSTEAD OF inserted trigger on it to handle inserting the data into the base table. The base table and the view include a column that can be null (like an identity column). When I insert into the view without passing in a value for the nullable column, I get an error back stating that I must supply a value (even though the column is an identity column). Is there any way around this?

Thanks CT

ctadlock wrote:

I'm not sure how GUIDs would help my situation.

I tried changing the "Sequence Name" value of the identity fields to @@identity and setting the IsIdentity flag to true. This almost worked; now a SQL issue is stopping me...

I have a view that has an INSTEAD OF inserted trigger on it to handle inserting the data into the base table. The base table and the view include a column that can be null (like an identity column). When I insert into the view without passing in a value for the nullable column, I get an error back stating that I must supply a value (even though the column is an identity column). Is there any way around this?

GUID's help because you then set the PK before saving the entity, and you don't have to read back the value into the entity. That's the real problem, reading back the value.

After MUCH playing around and researching online I've finally come up with a workable solution to getting entities mapped to views working correctly. The crux of the whole process if writing an updatable view correctly. Check out my blog posting for a full write-up on how to do this. I belive that mapping entities onto updatable views is a super powerful and flexible solution which gives the best of the O/R and stored procedure worlds.

http://community.rampgroup.com/blogs/ramp_technology_group/archive/2005/05/10/16.aspx

In addition to writing the views correctly, you have to make some changes to the generated LLBLGEN code.

1. The PK/IDENTITY column in the view must be marked as an IDENTITY column in LLBLGen's properties of the view. This is so the LLBLGen knows to fetch the IDENTITY value after the insert.

2. Since the INSTEAD OF triggers do the actual work of inserting the data into the table, you must change over use @@IDENTITY instead of SCOPE_IDENTITY(). I've done several tests on this to verify it's correctness. You will only run into issues if you have additional AFTER triggers on your table that insert into tables with IDENTITY columns, or if you have multiple threads using the same DataAdapter.

So you have to change to the code...

private static PersistenceInfoBucket CreateAccountViewEntityBucket()
        {
            PersistenceInfoBucket toReturn = new PersistenceInfoBucket();
            toReturn.AddFieldPersistenceInfo("AccountID", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountID", false, (int)SqlDbType.Int, 0, 0, 10, false, "SCOPE_IDENTITY()"));
            toReturn.AddFieldPersistenceInfo("Username", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "Username", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
            toReturn.AddFieldPersistenceInfo("AccountTypeID", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountTypeID", false, (int)SqlDbType.Int, 0, 0, 10, false, ""));
            toReturn.AddFieldPersistenceInfo("AccountTypeName", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountTypeName", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
            toReturn.AddFieldPersistenceInfo("Password", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "Password", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));

            return toReturn;
        }

to..

private static PersistenceInfoBucket CreateAccountViewEntityBucket()
        {
            PersistenceInfoBucket toReturn = new PersistenceInfoBucket();
            toReturn.AddFieldPersistenceInfo("AccountID", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountID", false, (int)SqlDbType.Int, 0, 0, 10, true, "@@IDENTITY"));
            toReturn.AddFieldPersistenceInfo("Username", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "Username", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
            toReturn.AddFieldPersistenceInfo("AccountTypeID", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountTypeID", false, (int)SqlDbType.Int, 0, 0, 10, false, ""));
            toReturn.AddFieldPersistenceInfo("AccountTypeName", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountTypeName", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
            toReturn.AddFieldPersistenceInfo("Password", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "Password", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));

            return toReturn;
        }

It would be nice if these values could be edited in the LLBLGen UI? (hint hint).

Thanks CT

Thanks for the feedback!

I'll see how I can add this to the designer. (enabling Identity and making sure the sequence is @@IDENTITY. )

Any progress on making the changes to the project designer to support this? It would be optimal to be able to set the attributes for a field...

Is computed Is nullable Is identity Sequence name

Thanks CT

They're among the features added to the 1.0.2005.1 upgrade, to address inflexibilities in the designer as much as possible.

Nice. For now I created a quick Windows Forms application to set these attributes for me. I used the LLBLGen dlls to load my project file...

            Project projectToInspect = Project.Load(filename);

            if (this.projectToInspect != null)
            {
                foreach (SD.LLBLGen.Pro.ApplicationCore.Entities.EntityDefinition entity in this.projectToInspect.Entities)
                {
                    if (entity.Target.ElementType == SD.LLBLGen.Pro.DBDriverCore.EntityMapTargetElementType.View)
                    {
                        foreach (SD.LLBLGen.Pro.ApplicationCore.Entities.EntityFieldDefinition field in entity.Fields)
                        {
                            if (field.IsPrimaryKeyField)
                            {
                                field.IsIdentity = true;
                                field.IdentityValueSequenceName = "@@IDENTITY";
                            }
                        }
                    }
                }
            }

Pretty simple. It's nice to have a good object model to work with.

Thanks CT

You can also wrap it in a plugin So you don't have to run a separate .exe

I'll probably cut this feature for 1.0.2005.1, as there's no more time. Though it might be addable, I'll try. If not, it's moved to the v2.0 list.

I've added the option to the upcoming 1.0.2005.1 to set an entity field to a sequence other than SCOPE_IDENTITY() or @@IDENTITY, you can now select one. Also fields of entities mapped onto views can be set to a sequence (either one).