Info:

V.2.6 Final, October 6th 2008 SQL Server 2005 Adapter Template/Linq to Sql

Bug: When mapping views to entities the MappedFieldIsNullable property is always false even though i can see in SQL Server Management Studio the field allows nulls. Take for example the Northwind database, create a LLBLGen project file and add entities mapped on the views in the Northwind database.

Now to give you a bit of background i encountered the issue when using the Linq to Sql templates which use the MappedFieldIsNullable to determine whether nulls are allowed. I was using a grid to display certain entities but some of the views wouldn't work and was complaining about not being able to assign a value of null to certain fields. I tracked it back to the LLBLGen and looked through the properties of entities and fields and found that the MappedFieldIsNullable value was incorrect and should have been true for some of the fields.

Going back to Northwind and the mapped views to entities you can see in SqlServer that the view AlphabeticalListOfProducts has many fields which allow nulls including SupplierID and CategoryID. Now open Template Studio with your saved LLBLGen project file and open up the entities and field the mapped view entity of AlphabeticalListOfProducts and take a look through the fields and look at the MappedFieldIsNullable and you can see the problem as SupplierID and all the other fields which allow nulls have a MappedFieldIsNullable value of false when it should be true.

Thanks

I think that as you have the entity mapped on to a view, the database view's metadata misses the information whether this field is nullable or not in the underlying table.

You should check the IsNullable option at Designer for the fields you want to behave as nullables.

Using SQL Server SMO i can retrieve the correct nullable value for the fields in the view so why you can't get the right value I leave to you guys. Onto your next point I have already used the IsNullable for the time being and modified the Ling to Sql templates to use that instead of the MappedFieldIsNullable however checking all the fields of all the views is not really practical.

SqlServer doesn't update the nullability state of a view field when the nullability of a table field changes (which is in a view). this thus leads to errors, hence we commented out the code in the driver.

In the SDK sourcecode: SqlServerSchemaRetriever.cs, line 1263 and further, there's a commented out alternative routine. It was commented out because it could sometimes hang. The current routine uses INFORMATION_SCHEMA, which is the right way to get view meta-data. Since in some cases Sqlserver doesn't update view field meta-data when a table field (which is inside the view) changes. So what you want isn't possible: it could work initially, but it is unreliable in the long run when fields change (fields would still be reported as nullable or still not reported as nullable).

SMO is a com-based library used by sql server management tools, so not an option for us, SMO would otherwise be required to be installed and it performs the same queries anyway.

your right SqlServer issue, oh well thanks for your help

I don't agree at all with the argument that "is nullable" should not be set automatically for entities based on a view (even with the SQL issue mentioned).

Your same argument could be made that LLBLGen should not set the datatype of fields in entities based on views either, because if the datatype of a column in the underlying table changes, the view will not know about that either. Similarly, if they remove a column from the underlying table, the view won't know about that either, so maybe LLBLgen shouldn't create the fields either...

No - it makes sense for LLBLGen to do what it always does, which is make our life easier. Developers/DBAs should already know that if they change a table, they probably need to refresh the views and stored procedures/triggers that may reference it. And if they do that, they need to refresh the LLBLgen Catalog as well. Don't turn a DB server knowledge issue into an LLBLGen feature issue, just document the well known DB issue.

And b.t.w. forcing the developer to set the nullabilty manually doesn't solve the problem at all (the view will still go out of sync if the underlying table changes). Why not just LLBLGen do the work for us in the off chance that we all know what we are doing ;-) ?

There's not a lot we can do. We enabled it but it gave other problems, like people having problems with changed underlying table field information as described above, and asked us to fix it, which wasn't our problem, as we just read meta-data.

As described above, you can enable it for yourself in a custom build of the driver.

In v3. we use a different way to obtain meta-data for views and it's available by default there, as the meta-data we obtain for a view already contains this info so we simply use it as -is.

Still, this is a bit unreliable, as sqlserver simply doesn't update the meta-data of the view if a field in the table in the view changes. So I agree that if we all know what we're doing it would be easy but the thing is: if the meta-data doesn't show up properly in the designer, it will still something asked to us why it doesn't show the right meta-data, but as said above, that's unfortunately not something we can solve: the right meta-data is not available as it's not up to date.

Sounds great to me if its making it into v3!! (hehe, I've managed to reveal another small feature planned for V3 )

SQL Server provides sp_refreshView, so I believe people can just call that against any views that might be out of sync to make sure the SQL information is up-to-date for consumption by LLBLGen v3.