Question

I would like to have a [Field] with a [FieldMapping] on for a foreign key, both on the column name and the Entity association...Is this possible?

ie.

[Field]
[FieldMapping("CompanyID")]
CompanyID

[Field]
[Association]
[FieldMapping("CompanyID")]
Company

Is this possible? Domain.Build throws an exception when I do this. I would like this specifically for the situation of a join that doesn't have a reverse mapped association:

from i in Query.All<SomeEntity>() join p in Query.All<Person>() on i.CompanyID equals p.CompanyID

Also, can I have multiple associations mapped on a field like:

[Field]
[Association]
[FieldMapping("ContactID")]
Contact SomeContact1

[Field]
[Association]
[FieldMapping("ContactID")]
Contact SomeContact2

Thank you. Thanks.

Updated at 25.02.2010 23:02:45

Domain.Build throws an exception when I put FieldMappingAttribute of "CompanyID" on Field Company and Field CompanyID...I'm ok with joining on Company.ID...but it won't go and fetch the whole related row, will it?

Also, I'm getting closer I think (the domain.Build takes longer)...but now I have the following exception calling domain.Build(config)

at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
   at System.ThrowHelper.ThrowArgumentOutOfRangeException()
   at System.Collections.Generic.List`1.get_Item(Int32 index)
   at Xtensive.Core.Collections.CollectionBaseSlim`1.get_Item(Int32 index)
   at Xtensive.Sql.SqlServer.v09.DataTableProxy.GetColumn(Int32 dbColumnIndex)
   at Xtensive.Sql.SqlServer.v09.Extractor.ExtractIndexes()
   at Xtensive.Sql.SqlServer.v09.Extractor.ExtractCatalogContents()
   at Xtensive.Sql.SqlServer.v09.Extractor.ExtractSchema(String schemaName)
   at Xtensive.Sql.SqlDriver.ExtractSchema(SqlConnection connection, String schemaName)
   at Xtensive.Storage.Providers.Sql.Driver.ExtractSchema(SqlConnection connection)
   at Xtensive.Storage.Providers.Sql.SchemaUpgradeHandler.GetStorageSchema()
   at Xtensive.Storage.Providers.Sql.SchemaUpgradeHandler.GetExtractedSchema()
   at Xtensive.Storage.Building.Builders.DomainBuilder.SynchronizeSchema(SchemaUpgradeMode schemaUpgradeMode)
   at Xtensive.Storage.Building.Builders.DomainBuilder.BuildDomain(DomainConfiguration configuration, DomainBuilderConfiguration builderConfiguration)
   at Xtensive.Storage.Upgrade.UpgradingDomainBuilder.BuildStageDomain(UpgradeStage stage)
   at Xtensive.Storage.Upgrade.UpgradingDomainBuilder.Build(DomainConfiguration configuration)
   at Xtensive.Storage.Domain.Build(DomainConfiguration configuration)
   at Ipreo.Horizon.Data.Providers.Advanced.DataContext..cctor() in C:\Development\Ipreo Horizon\Source\Main\Ipreo.Horizon.Data.Providers.Advanced\DataObjectsSessionAdapter.cs:line 24

Updated at 26.02.2010 2:00:16

I'm running on a an database that I've never touched before with about 600 tables. I wrote/hacked a T4 script to transform an existing linq2sql dbml file for this DB schema into DataObjects attributed classes.

I'm running upgrademode as Validate only.

I'll get you a full schema sample shortly, in case the above doesn't give enough info.

Also...if DataObjects needs to create custom temp tables, is there a way to have it do so in memory only? Or on a different DB server? Access to the DB I want to query should be read-only.

Thanks.

Updated at 26.02.2010 14:36:07

I'm certainly open to other suggestions on how to implement this but what it comes down to is that it's not "my" database. In other words, I can't touch it...or do anything except read from it. I have a database that's mine that I can do whatever I want with on the same LAN on a different SQL Server...or maybe I could even make a second instance of SQL Server on the same physical box...but I can't alter their database.

I'd really like to leverage your LINQ support, but I don't think there is any flexibility on this requirement.

Thanks.

Updated at 26.02.2010 17:12:37

Ok, that's awesome, thanks. I'll use Legacy mode instead of Validate. I may be able to get around the access to tempdb...I was just under the misimpression that tables also needed to be created in the target DB too...

Working on getting you the schema for my error...

Updated at 26.02.2010 18:08:10

The schema export wizard is giving me a hard time for some reason...not sure why. But I loaded your pdbs and captured the following: index.CreateIndexColumn(table.GetColumn(columnId), !reader.GetBoolean(13)); is the source of the exception, in ExtractIndexes.

columnId = 14 query = "select t.schema_id, t.object_id, t.type, i.index_id, i.name, i.type, i.is_primary_key, i.is_unique, i.is_unique_constraint, i.fill_factor, ic.column_id, 0, ic.key_ordinal, ic.is_descending_key, ic.is_included_column, NULL, NULL from sys.indexes i inner join (select schema_id, object_id, 0 as type from sys.tables union select schema_id, object_id, 1 as type from sys.views) as t on i.object_id = t.object_id inner join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id where i.type < 3 and not (ic.key_ordinal = 0 and ic.is_included_column = 0) and schema_id = 1 order by t.schema_id, t.object_id, i.index_id, ic.is_included_column, ic.key_ordinal"

Debug logs around the exception...it looks like a replication table....I'll try removing it from my model...please let me know if this helps...thanks.:

45.28s/TWorkItemThread1  Info Modelling                +ForeignKeys:
 45.29s/TWorkItemThread1  Info Modelling                None
 45.29s/TWorkItemThread1  Info Modelling                +FullTextIndexes:
 45.29s/TWorkItemThread1  Info Modelling                None
 45.29s/TWorkItemThread1  Info Modelling                TableInfo "./Tables/dbo.tbl_repl_pub_table_fido"
 45.29s/TWorkItemThread1  Info Modelling                +Columns (4):
 45.29s/TWorkItemThread1  Info Modelling                ColumnInfo "./Tables/dbo.tbl_repl_pub_table_fido/Columns/pub_cat_id"
 45.29s/TWorkItemThread1  Info Modelling                +Type = Type: Int32, Length: null (TypeInfo)
 45.30s/TWorkItemThread1  Info Modelling                +DefaultValue = 0 (Int32)
 45.30s/TWorkItemThread1  Info Modelling                ColumnInfo "./Tables/dbo.tbl_repl_pub_table_fido/Columns/tab_nm"
 45.30s/TWorkItemThread1  Info Modelling                +Type = Type: String, Length: 4000 (TypeInfo)
 45.30s/TWorkItemThread1  Info Modelling                ColumnInfo "./Tables/dbo.tbl_repl_pub_table_fido/Columns/audit_all_columns_ind"
 45.30s/TWorkItemThread1  Info Modelling                +Type = Type: Boolean, Length: null (TypeInfo)
 45.30s/TWorkItemThread1  Info Modelling                +DefaultValue = False (Boolean)
 45.31s/TWorkItemThread1  Info Modelling                ColumnInfo "./Tables/dbo.tbl_repl_pub_table_fido/Columns/repl_type"
 45.31s/TWorkItemThread1  Info Modelling                +Type = Type: Byte, Length: null (TypeInfo)
 45.31s/TWorkItemThread1  Info Modelling                +DefaultValue = 0 (Byte)
 45.31s/TWorkItemThread1  Info Modelling                +PrimaryIndex:
 45.31s/TWorkItemThread1  Info Modelling                PrimaryIndexInfo "./Tables/dbo.tbl_repl_pub_table_fido/PrimaryIndex (PK_tbl_repl_pub_table_fido)"
 45.31s/TWorkItemThread1  Info Modelling                +IsUnique = True (Boolean)
 45.31s/TWorkItemThread1  Info Modelling                +KeyColumns (2):
 45.31s/TWorkItemThread1  Info Modelling                0: KeyColumnRef "./Tables/dbo.tbl_repl_pub_table_fido/PrimaryIndex/KeyColumns/0"
 45.32s/TWorkItemThread1  Info Modelling                +Direction = Positive (Direction)
 45.32s/TWorkItemThread1  Info Modelling                +Value = ./Tables/dbo.tbl_repl_pub_table_fido/Columns/pub_cat_id (ColumnInfo)
 45.32s/TWorkItemThread1  Info Modelling                1: KeyColumnRef "./Tables/dbo.tbl_repl_pub_table_fido/PrimaryIndex/KeyColumns/1"
 45.32s/TWorkItemThread1  Info Modelling                +Direction = Positive (Direction)
 45.32s/TWorkItemThread1  Info Modelling                +Value = ./Tables/dbo.tbl_repl_pub_table_fido/Columns/tab_nm (ColumnInfo)
 45.32s/TWorkItemThread1  Info Modelling                +ValueColumns (2):
 45.32s/TWorkItemThread1  Info Modelling                ValueColumnRef "./Tables/dbo.tbl_repl_pub_table_fido/PrimaryIndex/ValueColumns/0"
 45.32s/TWorkItemThread1  Info Modelling                +Value = ./Tables/dbo.tbl_repl_pub_table_fido/Columns/audit_all_columns_ind (ColumnInfo)
 45.32s/TWorkItemThread1  Info Modelling                ValueColumnRef "./Tables/dbo.tbl_repl_pub_table_fido/PrimaryIndex/ValueColumns/1"
 45.33s/TWorkItemThread1  Info Modelling                +Value = ./Tables/dbo.tbl_repl_pub_table_fido/Columns/repl_type (ColumnInfo)
 45.33s/TWorkItemThread1  Info Modelling                +IsPrimary = True (Boolean)
 45.33s/TWorkItemThread1  Info Modelling                +SecondaryIndexes:
 45.33s/TWorkItemThread1  Info Modelling                None
 45.33s/TWorkItemThread1  Info Modelling                +ForeignKeys:
 45.33s/TWorkItemThread1  Info Modelling                None
 45.33s/TWorkItemThread1  Info Modelling                +FullTextIndexes:
 45.34s/TWorkItemThread1  Info Modelling                None
 45.34s/TWorkItemThread1  Info Modelling                +Sequences (3):
 45.34s/TWorkItemThread1  Info Modelling                SequenceInfo "./Sequences/Int32-Generator"
 45.35s/TWorkItemThread1  Info Modelling                +Seed = 128 (Int64)
 45.35s/TWorkItemThread1  Info Modelling                +Increment = 128 (Int64)
 45.35s/TWorkItemThread1  Info Modelling                +Type = Type: Int32, Length: null (TypeInfo)
 45.35s/TWorkItemThread1  Info Modelling                SequenceInfo "./Sequences/Int64-Generator"
 45.35s/TWorkItemThread1  Info Modelling                +Seed = 128 (Int64)
 45.36s/TWorkItemThread1  Info Modelling                +Increment = 128 (Int64)
 45.36s/TWorkItemThread1  Info Modelling                +Type = Type: Int64, Length: null (TypeInfo)
 45.36s/TWorkItemThread1  Info Modelling                SequenceInfo "./Sequences/Byte-Generator"
 45.36s/TWorkItemThread1  Info Modelling                +Seed = 128 (Int64)
 45.37s/TWorkItemThread1  Info Modelling                +Increment = 128 (Int64)
 45.37s/TWorkItemThread1  Info Modelling                +Type = Type: Byte, Length: null (TypeInfo)

More: It's a view now that's causing the problem: return view.ViewColumns[dbColumnIndex]; dbColumnIndex=14 vw_pr_company_focus_element is the object name...so it's a view.

Does DO not support views?

Updated at 27.02.2010 22:14:52

I'm sure it's an overstatement that implementing stored proc support is pretty simple, but fortunately I need only to support SQL server for now....

I already have my own wrapper QueryProvider, IQueryable implementation, and fast serializer (with what I think you call O2O mapping) for WCF/Silverlight/POCO support, and my dbml file already generates the class definitions for the sotred proc and view result sets. So, I'm thinking all I really need to do is make my expression visitor on my server use SqlClient to execute queries against stored procs and views, read them out into their class equivalents and then swap them back into the expression tree as constants. Sorry if that was a little unclear.

Please let me know if you need any additional info from me to help get the issue (index out of range) resolved with extracting the metadata from the SQL schema.

Thank you.

Updated at 01.03.2010 16:01:59

It's failing at the following line in SqlModelConverter

type = sqlValueType.Type.ToClrType();

because sqlValueType is smalldatetime and Type is Unknown.

If that sheds any light on it, I'd be more than happy to make the code fix myself if you could point me in the right direction.

Thanks.

Updated at 02.03.2010 17:53:17

Hi,

Sorry for the confusion. I tried to generate scripts for my schema to email to you...but I got a strange error from SQL Management Studio indicating something to the effect of my schema is invalid! It's not my database, so I have no idea how this could happen... Anyway, I restored a fresh copy of the DB and ran the export again and I was able to generate scripts successfuly...and I also got past that error in DO....so really I don't know what happened.

I'm now up to a much more promising error (I hope). Upon building the domain, I'm told (SchemaSynchronizationException): Legacy schema is not compatible ( (can't change type of column 'Tables/tbl_pr_ben_fund_company_holding/Columns/holding_dttm')).

var config = new DomainConfiguration("sqlserver", ConfigurationManager.ConnectionStrings["Data.Providers.Vad.Properties.Settings.ConnectionString"].ConnectionString);
            config.UpgradeMode = Xtensive.Storage.DomainUpgradeMode.Legacy;
            config.ValidationMode = ValidationMode.OnDemand; // this doesn't seem to help the validation in the way I want...
            config.AutoValidation = false; // this doesn't seem to help the validation in the way I want...

            config.Types.Register(Assembly.GetExecutingAssembly(), _entitiesNamespace);
            try
            {
                _domain = Domain.Build(config);
            }
            catch (Exception ex)
            {
            }

Interesting thing is my model has this listed as a DateTime field...SQL has it listed as a smalldatetime field (the same time that I think was giving problems before...). It's SQL Server 2005. I'm emailing you my schema, model, dbml file, and T4 script to the address provided.

Additionally, and more importantly, I can't rule out the fact that someone may make changes to this DB without my model getting updated. Is there a way to make DO validate the SQL schema matches the class schema on demand, not all on Domain.Build(), in Legacy mode? This would be critical for me, as all of my query support will break all the time if someone changes any table of the 500ish in the DB... I would like to still be able to query as long as the tables I'm querying are still valid...

This thread was imported from our support forum. The original discussion may contain more detailed answer. Original topic by JeffN.

Answer 1

Alex (Xtensive) wrote:

> I would like to have a [Field] with a [FieldMapping] on for a foreign key, both on the column name and the Entity association...Is this possible?

We have the following code in tests:

[Field, FieldMapping("BookAuthor")]
    [Association(OnTargetRemove = OnRemoveAction.Deny)]
    public Author Author { get; set; }

So this is definitely possible. But you shouldn't (and can't) split declaration of field and the association.

> Also, can I have multiple associations mapped on a field like...

This is not possible. What is your actual purpose? Having an association (reference field), you can:

Get its underlying key via entity.Reference.Key (to get "unified" Key) or entity.Reference.KeyField (e.g. entity.Reference.Id). This works in LINQ queries (i.e. you can use this to write joins).
Get the underlying Key without resolving a reference using either protected Entity.GetReferenceKey(...) method or via Session.Services.Demand<directpersistentaccessor>().GetReferenceKey(...). Both methods aren't exposed directly because actually this is pretty rare case when this logic is necessary (it allows you to find out if there is a reference to some entity without actually resolving it - but normally resolving it is necessary as well, and there is Prefetch API allowing to do this efficiently).

Alex (Xtensive) wrote:

Btw, your query with join must look like (see all 3 options):

var option1 = 
    from e in Query.All<SomeEntity>() 
    join p in Query.All<Person>() on e.Company.ID equals p.Company.ID // Implied that ID field is marked by [Key]; 
    // bad way, because your query is dependent on association key structure.
    select new {SomeEntity = e, Person = p};

  var option2 = 
    from e in Query.All<SomeEntity>() 
    join p in Query.All<Person>() on e.Company.Key equals p.Company.Key // The same, but using unified Key; 
    // cool, because query does not depend on actual key structure, i.e. Key can be even composite.
    select new {SomeEntity = e, Person = p};

  var option3 = 
    from e in Query.All<SomeEntity>() 
    join p in Query.All<Person>() on e.Company equals p.Company // Yes, you can simply compare references as well. So this is another "unified" option.
    select new {SomeEntity = e, Person = p};

All these queries lead to absolutely the same SQL query in the end.

Alex (Xtensive) wrote:

> Domain.Build throws an exception when I put FieldMappingAttribute of "CompanyID" on Field Company and Field CompanyID...

The exception you get is actually thrown from schema extractor, so it must not be related to the original case.

Could you try to create some simple schema in the same database in Validate (it will fail, but with another exception) or Recreate (will destroy all the data, but must not lead to failure) UpgradeMode? If it will fail with the same exception, can you send us a backup of the database you use, or just its schema w/o data?

> I'm ok with joining on Company.ID...but it won't go and fetch the whole related row, will it?

No, it won't.

Alex (Xtensive) wrote:

> Also...if DataObjects needs to create custom temp tables, is there a way to have it do so in memory only?

In-memory - no. We use them only to transmit the data to SQL Server, but SQL Server does not understand our in-memory tables :( In fact, in-memory tables are useless with SQL Server provider.

> Or on a different DB server?

No, currently this is impossible, although this is possible in general. If this is really important for you, we can think about implementing it. But note:

We use standard notation for temporary tables (@table, #table), so they're really temporary (= destroyed on completion of session under any case, aren't visible to other connections, etc.). @ and # tables are created in tempdb, so your database is left untouched.
Placing such a table on a distant server will decrease the performance: I actually absolutely uncertain on how SQL Server will really deal with it. The best case is if it will simply copy it locally, but... It might decide to do something else - the query optimizer there is significantly based on heuristics, so everything is possible (e.g. I'd assume it might decide to send you own table to a distant server to perform join there).
Remember that such temporary tables are normally involved into joins or similar operations, so in general it's quite desirable to have them "right there".
Usage of this feature is actually completely under your control. AFAIK, currently there are no implicit ways that might lead to usage of temporary tables. Earlier prefetch API could use them, but currently it doesn't (that's actually more efficient there - it anyway splits the volumes into bulks of reasonable size). So basically, you must care about this only if you use a large collection of DTOs or identifiers in query. Since most of other ORMs don't support this at all, it's not really difficult to put the utilization of this feature under your own control...

Alex (Xtensive) wrote:

Temporary tables aren't actually created @ your database. They always created in tembdb. So you won't modify the original database.

Btw:

Long time ago we though about providing explicit read-only mode for Sessions (so non-intentional changes must be denied in this mode). May be this will be helpful in this case.
Another good alternative ensuring no changes are made to the database is to rollback the outermost transaction - by simply don't calling tx.Complete.

Alex (Xtensive) wrote:

If you use any upgrade mode except DomainUpgradeMode.Legacy:

Tables for generators are created if [KeyGenerator(KeyGeneratorKind.None)] isn't provided for each hierarchy
A set of Metadata.* tables is created to handle further upgrade.
DO considers it fully manages the schema in any of such modes, so if you'll allow this (= use Perform, PerformSafely or Recreate modes), it can make any changes there.

If you use DomainUpgradeMode.Legacy:

Tables\sequences for generators are NOT created, and thus just two key generators are available: for Guid and String type. They don't require a table\sequence to work. Currently we don't support autoincrement columns as key generators in this mode, although you can try manually getting their values using DirectSqlAccessor service (I know, this is really ugly and slow, but currently that's the only way to create such keys).
A set of Metadata.* tables is NOT created in this mode.
Any hierarchies with inheritance must use [TypeDiscriminator] attribute. Default mapping case with TypeId column for such hierarchies will fail on comparison of expected & actual schemas, because (most likely) there is no TypeId column of integer type in your tables.
DO considers it cannot change the schema in this mode, and moreover, must use relaxed schema comparison rules. Such things as absence of index or foreign key or different (but compatible) column type are ignored.

Legacy upgrade mode was is actually designed to cover nearly your cases:

DO must not change the schema
Most likely, it should be able mainly to read the data // That's why we support just the simplest cases with key generators

Further this must evolve to full support of ActiveRecord pattern:

An integrated VS.NET tool shipped with DO must be able to extract the database schema (that's what we already can do) and save it - may be even as .emdx (LOL, but we found this is may be the shortest path to bring the designer there - i.e. we can simply "borrow" it from EF).
A T4 template will generate the code of our persistent types by extracted schema (or by .emdx file).

Alex (Xtensive) wrote:

> Does DO not support views?

Yes, it does not allow to map entities to them, but it should behave properly while extracting the info about them.

We'll try to isolate the problem related to view info extraction tomorrow.

> Also, stored proc resultsets would be a nice to have.

The same - DO does not support mapping to stored procedures :(

> It's pretty easy to implement myself... and I'll need to, but linq to sql does it.

I fear it won't be a piece of cake, especially if you need database-independent solution there. If you'll deal just with SQL Server, using LINQ to SQL could really help here.

In short, I've made a conclusion full support of various mapping scenarios for legacy databases is really important. Currently we have the following lacks:

Autoincrement columns
Views / materialized views
Stored procedures.

Last two options must not be really complex for us (we already discussed this today) - most likely in all the cases here we must return DTOs, but mapping of query result and arbitrary DTOs is already fully supported (this is the most complex part). So we must mainly make all the decisions about the API, and this is actually a hard part:

Storage procedures are interesting only in Legacy mode
The same is about usual views - with LINQ you can create "LINQ views" dynamically by e.g. simply storing a queryable in static field.
But providing support for materialized views seems attractive in both cases, and we really can provide full support for this feature.

I'm not sure if we can prioritize this - I feel we must implement already planned features first (security, better WPF\WF\ASP.NET MVC support, VS2010 & .NET 4, PostSharp 2.0), but I'd like to eliminate all really blocking issues like problem with view info extractions ASAP.

Alex (Xtensive) wrote:

> If I can get this working, I'll give you my T4 script that transforms the dbml file (not that it was that much work...)

That would be cool, thanks :)

Denis Krjuchkov (Xtensive) wrote:

Hello, JeffN. I have some questions regarding your issue with smalldatetime. Are you using SQL Server 2005 or 2008? Are you still using Legacy upgrade mode? Can you provide full stack trace? It will also be very helpful, if you provide persistent class definition and corresponding table definition. If you don't want to publish your code here, send me a email to denis.krjuchkov at dataobjects dot net. Thanks for helping improving DataObjects.Net :wink:

Denis Krjuchkov (Xtensive) wrote:

Denis, see viewtopic.php?f=29&t=5784&p=13987#p13962 Full stack trace is here. How does that stack trace relate with this error? I guess these issues are different :wink: It's failing at the following line in SqlModelConverter type = sqlValueType.Type.ToClrType(); because sqlValueType is smalldatetime and Type is Unknown.

Alex (Xtensive) wrote:

Just talked with Denis, few conclusions:

Possibly it will take more that 1 day to analyze the schema - it is really large.
We really need to add DomainUpgradeMode.UnsafeLegacy (w/o any validation). Must be pretty simple, we'll try to add this in the nearest days.

Alex Kofman wrote:

Would you please post something here when the new domain upgrade mode without validation is in place? Or is there an update list I can check to see when this has been released? It's a blocking issue for me. You can star issue in the bug-tracker to subscribe on its status update (-:

Denis Krjuchkov (Xtensive) wrote:

No, it does not. As Alex Kofman already suggested, you can monitor progress of this issue here. http://code.google.com/p/dataobjectsdot ... ail?id=613

Updated at 25.02.2010 23:02:45

Updated at 26.02.2010 2:00:16

Updated at 26.02.2010 14:36:07

Updated at 26.02.2010 17:12:37

Updated at 26.02.2010 18:08:10

Updated at 27.02.2010 22:14:52

Updated at 01.03.2010 16:01:59

Updated at 02.03.2010 17:53:17

Alex (Xtensive) wrote:

Alex (Xtensive) wrote:

Alex (Xtensive) wrote:

Alex (Xtensive) wrote:

Alex (Xtensive) wrote:

Alex (Xtensive) wrote:

Alex (Xtensive) wrote:

Alex (Xtensive) wrote:

Denis Krjuchkov (Xtensive) wrote:

Denis Krjuchkov (Xtensive) wrote:

Alex (Xtensive) wrote:

Alex Kofman wrote:

Denis Krjuchkov (Xtensive) wrote:

Subscription:

Related questions