Getting a validation warning because a view mapped to an entity has Fields marked as optional but the underlying mapped field is not nullable. Since views do not offer Nullable information, can we have the ability to mark the fields as is nullable?

On the subject of warnings, any way to shut some of these off? Besides the above, orphaned fields and tables would be nice. We are doing a rewrite and only adding tables/columns we actually use, so there are a lot of orphans. Makes it hard to see any real issues.

Brian

Getting a validation warning because a view mapped to an entity has Fields marked as optional but the underlying mapped field is not nullable. Since views do not offer Nullable information, can we have the ability to mark the fields as is nullable?

Which Traget Framework are you using?

On the subject of warnings, any way to shut some of these off? Besides the above, orphaned fields and tables would be nice. We are doing a rewrite and only adding tables/columns we actually use, so there are a lot of orphans. Makes it hard to see any real issues.

Please check the available options for the following Project Properties. - ExcludableOrphanedElementDetectedAction The action to perform when the system detects an 'orphaned' excludable relational model data element (element which is not a mapping target). If the specified value isn't applicable for the specific element, ExcludeFromProject is used instead. - NonExcludableOrphanedElementDetectedAction The action to perform when the system detects an 'orphaned' non-excludable relational model data element (element which is not a mapping target). If the specified value isn't applicable for the specific element, RaiseError is used instead.

Using the LLBLGen Pro Runtime Framework.

Just tried the round trip and looks like 3.0 is now able to pull the nullable attribute from SQL Server. This is good.

Sometimes though I want a nullable column to not be nullable, meaning I want the default value for the type (usually boolean, so false).

Anyway to stop the refresh from auto updating the IsOptional column during refresh?

EDIT: Saw another thread (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18005) about the IsOptional and Stored Proc Params not remembering. Downloaded and installed the 6/24 build (3.0.10.520). Still an issue with the views.

please be specific what's still an issue: nullability with views or proc parameters.

The problem with sqlserver is that once the view is created, it doesn't update nullability info about fields returned by the view if these change in the underlying table. So if your view at creation time didn't have any nullable fields, it won't get them when you change the fields in the underlying table(s).

So we can only obtain what sqlserver tells us in the meta-data. If you think a view field should be nullable / shouldn't be nullable while it's reported wrong, simply go to SSMS of sqlserver and update the view (edit the view, save).

If this isn't the problem you're referring to, please let us know what is more specifically. Sorry if you already did and we misunderstood it.

I just need to be able to override with IsOptional and have it keep that setting after the database refreshes so I do not have to deal with Nullable<> in code.

I understand the meta-data issue, but many views are legacy but the data is actually correct. I would love to be able to fix all views/columns but just not feasible.

Brian

brianchance wrote:

I just need to be able to override with IsOptional and have it keep that setting after the database refreshes so I do not have to deal with Nullable<> in code.

I understand the meta-data issue, but many views are legacy but the data is actually correct. I would love to be able to fix all views/columns but just not feasible.

Brian

If you set the entity columns to not optional, you likely will get warnings, but you can ignore those. Do you get errors or warnings in that case?

To make me understand things correctly: you have legacy views, which are reported by sqlserver as having nullable fields, but actually don't have that. You want to map entities on these views without having nullable fields in the entities, correct? And after you refresh, the refresher resets your work and makes some fields optional again, something you don't want, correct?

Do you get errors or warnings in that case?

Getting warnings, happy to ignore.

You want to map entities on these views without having nullable fields in the entities, correct? And after you refresh, the refresher resets your work and makes some fields optional again, something you don't want, correct?

Exactly, just need to override as necessary.

brianchance wrote:

Do you get errors or warnings in that case?

Getting warnings, happy to ignore.

You want to map entities on these views without having nullable fields in the entities, correct? And after you refresh, the refresher resets your work and makes some fields optional again, something you don't want, correct?

Exactly, just need to override as necessary.

Ok. Currently this isn't going to work, nullability is always reflected in the entity after a refresh. The idea behind that is that if a field is potentially nullable you will get errors at runtime if you store a null in a non-nullable field.

I have a question though (to make me understand why one would need this): you say it's a legacy database: why refreshing the catalog then?

Probably mis-used the term legacy, lets just say it is old and crappy. Besides, at some point someone will update the catalog which will cause all settings to be lost.

Only need the nullability on views, which we are not doing updates with (I set all columns to read only). Even if I did updates, I would have to do instead of triggers because the views are very complicated.

Please advise, I have 4 developers waiting for me to get the project converted. Having to update 25-30 views is going to be rough.

Brian

We have to add an option for that, there's no other way to make this automatic.

In the mean time, load the project, after refresh, open Element Search, select 'Entity' and run this code:

var entitiesMappedOnView = from kvp in p.GetAllEntitiesWithTheirMappingsForDatabase(SqlServerSqlClientDriverID)
                           where kvp.Value!=null && kvp.Value.MappedTarget.ElementType==ProjectElementMapTargetElementType.View
                          select kvp.Key;
foreach(EntityDefinition entity in entitiesMappedOnView)
{
    foreach(FieldElement field in entity.Fields)
    {
        field.IsOptional = false;
    }
}

return p.EntityModel.Vertices;

This will set the IsOptional flag for all fields in all entities mapped onto a view to false.

Of course, when someone refreshes, you've to re-run this code. We'll see how we can add an option to the project / driver for this for v2.x migration projects.

I really do not want ALL fields on all views marked IsOptional, just the ones I select in the UI.

It seems like you did this for the stored procedures? http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18005

brianchance wrote:

I really do not want ALL fields on all views marked IsOptional, just the ones I select in the UI.

It seems like you did this for the stored procedures? http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18005

That's a slightly different issue: as the nullability of parameters isn't determinable, we make a choice: not nullable. As some are nullable, the user has to override this. As the db value is always not nullable, the mismatch thus has to be made by the user manually, and is ignored. This keeps the manual edit as-is after the refresh.

With views this is a different issue: these can change on the db side, and thus a mismatch can be caused by either the target view changed OR a manual edit.

I must say I fail to see what you want. First you say you don't want to deal with nullable<T>, now you don't care for some fields, and do for others. If you don't care for some fields to be nullable<T>, why can't you accept the nullable flag as it's set by the refresher?

We thought about it, but it is really a rare, edge case where you simply don't want to work with the nullability of a field, which is nullable (according to the meta-data) but not in reality (as you say, the data apparently doesn't contain nulls)

Anyway, I'll see what I can do, by comparing old version with new version and the mapped field, but I can't promise anything.

Ok, I've made the following change in the next build (June 28th) - when an entity field or typedview field has a different value for IsOptional than its original target field, it's assumed it's set manually (which is possible when the target isn't a table) - when the IsOptional flag value differs from the one of the new target field, it's synced when the flag isn't set manually (see point one) or when the target is a table.

I can now change the IsOptional flag values in an entity mapped onto a view, and refresh, which keeps the manually changed flags, and when I do so in an entity mapped onto a table, I get the flag reset (after I've exported ddl sql and tossed it away) after the refresh as it should, as the target is a table.

I must say I fail to see what you want. First you say you don't want to deal with nullable<T>, now you don't care for some fields, and do for others. If you don't care for some fields to be nullable<T>, why can't you accept the nullable flag as it's set by the refresher?

The real issue is bool flags where null has always meant false and having the DAL do the work has been fine (especially since 2.x could not determine if a view column was nullable). We really meant null = false, but if you use complicated case statements that I know will return a value, SQL sometimes still marks the column nullable.

Instead of:

if (entity.SomeFlag)

we would have to do:

if (entity.SomeFlag.GetValueOrDefault())

While normally not a big deal, it is with an established code base. If 2.x had been enforcing nullablility on the views, they would have been coded differently. We will be doing that going forward.

Ok, I've made the following change in the next build (June 28th)

I will download the new build when it is available and give it a try. Is there anyway to get a notice when a new build is posted?

You might be able to narrow the case to only care if IsOptional = false and the database field is null. I have a hard time seeing how a database field could be not null but allowed to be optional.

Thank you for getting this out.

Brian

brianchance wrote:

I must say I fail to see what you want. First you say you don't want to deal with nullable<T>, now you don't care for some fields, and do for others. If you don't care for some fields to be nullable<T>, why can't you accept the nullable flag as it's set by the refresher?

The real issue is bool flags where null has always meant false and having the DAL do the work has been fine (especially since 2.x could not determine if a view column was nullable). We really meant null = false, but if you use complicated case statements that I know will return a value, SQL sometimes still marks the column nullable.

Instead of:

if (entity.SomeFlag)

we would have to do:

if (entity.SomeFlag.GetValueOrDefault())

While normally not a big deal, it is with an established code base. If 2.x had been enforcing nullablility on the views, they would have been coded differently. We will be doing that going forward.

Ok, I've made the following change in the next build (June 28th)

I will download the new build when it is available and give it a try. Is there anyway to get a notice when a new build is posted?

Expect it to be live within 2 hours or so.

You might be able to narrow the case to only care if IsOptional = false and the database field is null. I have a hard time seeing how a database field could be not null but allowed to be optional.

Same case, with views which are reported wrong by the db engine. For tables, indeed they have to be in sync, which is a requirement still.

It's available.

Works!

The Refresh Relational Model often asks me to generate SQL scripts for changes, anyway to turn this off?

We have added foreign keys/navigators in LLBLGen that are not in the database, but do not want actual foreign keys because of old bad data. Mainly because we have old code inserting and cannot enforce said constraint. (Yes, I know we are terrible).

For most of our projects we work from the database up only, and want that to be the source.

Brian

If you want to keep refreshing but you don't want to add the FK constraints generated by the DDL SQL, (but please consider to apply them), set RelationshipsFollowDBForeignKeyConstraints to false in the project properties.

This has as disadvantage that added FK constraints to the relational database aren't added automatically as relationships, that's the consequence of it. Please see this post: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=101511&ThreadID=18122