Hello,

I'm getting "System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized." from the topTwo.Query.Load() statement below.

public bool LoadSecondItem(Guid captureJobId) {
	/* Get top 2 item Ids */
	CaptureJobSinglePageItemCollection topTwo = new CaptureJobSinglePageItemCollection();
	topTwo.Query.Select(topTwo.Query.Id);
	topTwo.Query.Where(topTwo.Query.CaptureJobId.Equal(captureJobId));
	topTwo.Query.OrderBy(topTwo.Query.ItemOrder.Ascending);
	topTwo.Query.es.Top = 2;

	/* Load second item */
	if (topTwo.Query.Load() && topTwo.Count == 2) {
		return this.LoadByPrimaryKey(topTwo[1].Id.Value);
	}
	else {
		return false;
	}
}

Stack trace shows the occurs at:
EntitySpaces.Interfaces.esDataProvider.esLoadDataTable(esDataRequest request, esProviderSignature sig)

I'm using SQL2005 with ES 2007.0.913.0

Please tell me if there is other information I can provide to help resolve / workaround this issue.

 Thanks.

I am adding new functionality to an existing app and I can access data successfully except for the call in the first post.  In fact, I use that method to load an ES object in two places.  In one place it works fine, in the other I get the error.  I am using configless connection setup as follows...

public static void SetupEntitySpacesConnection() {
	ConnectionInfo ci = new ConnectionInfo(true);

	esConnectionElement conn = new esConnectionElement();
	foreach (esConnectionElement c in esConfigSettings.ConnectionInfo.Connections) {
		if (ci.EsName == c.Name) {
			conn = c;
			break;
		}
	}

	conn.CommandTimeout = ci.EsCommandTimeout;
	conn.ConnectionString = ci.GetUserLoginInfo(true).ConnectionString;
	conn.DatabaseVersion = ci.EsDatabaseVersion;
	conn.Name = ci.EsName;
	conn.Provider = ci.EsProvider;
	conn.ProviderClass = ci.EsProviderClass;
	conn.ProviderMetadataKey = ci.EsProviderMetadataKey;
	
	esSqlAccessType sat = esSqlAccessType.DynamicSQL;
	try {
		sat = (esSqlAccessType) Enum.Parse(typeof(esSqlAccessType), ci.EsSqlAccessType, true);
	}
	catch {}
	conn.SqlAccessType = sat;

	try {
		esConfigSettings.ConnectionInfo.Connections.Remove(conn);
	}
	catch {}
	esConfigSettings.ConnectionInfo.Connections.Add(conn);
	esConfigSettings.ConnectionInfo.Default = conn.Name;
}

The first two lines of Main() are:

EntitySpaces.Interfaces.esProviderFactory.Factory = new EntitySpaces.LoaderMT.esDataProviderFactory();
TheSystem.SetupEntitySpacesConnection();

The app is a .NET Windows Service and the code is a background thread.  It is the only thread I created though, just used to allow the service's OnStart method to return.  The call that fails is in an esTransaction scope while the one that works is not, but I have esTransactions that work fine - and other ES objects load fine in the same scope.

I hope that helps a bit more, please let me know what other information might help.

Thank you.

Mike.Griffin:

I'm pretty confused, if SetupEntitySpacesConnection is called only once during startup, what is it doing the very first thing looping through connections in a foreach loop?

I never really found a clear guide to using configless setup.  I think some of the stuff you saw was taken from a forum post.

Mike.Griffin:

Are you also trying to use a config file too?

No, there are no ES sections in the config file.

Mike.Griffin:

Also, what is going with that enum.Parse stuff?

esConnectionElement.SqlAccessType takes an enum, but my settings are saved as a string.  If there is a better way to go from a string to an enum I'm happy to change it.

Mike.Griffin:

Finally, it looks like you remove the connection and then immediately turn around and add it again?  If you are calling this more than once I certainly see how things can go wrong ?  Can you shed some light on this ...  Perhaps a database call is occuring when you have the connection removed.

This isn't called more than once unless the configuration changes.  I have a System.IO.FileSystemWatcher that watches a shared configuration file.  If the database connection settings are changed through a configuration utility then that shared file will be updated.  This triggers FileSystemWatchers in two windows services, one website, and two possible WinForms apps that may be running to update thier configuration without needing a restart.  The FileSystemWatchers are set to call SetupEntitySpacesConnection when the configuration file changes, the same function that is called when the app starts.  SetupEntitySpacesConnection is actually in a shared library.  I'm happy to change it to update a possibly existing connection in the proper way if you can tell me what you recommend.

Mike.Griffin:

esTransactionScope works fine in multi-threaded scenario's as every web page in ASP.NET is handled by a different thread and we have tons of folks doing web development.

Yeah, I know.  This has all been working fine up to this point.

Is there any optional logging that can be turned on in ES to give more details about the current state, or anything else you can think of that I should look for?

Thank you. 

We have not seen this bug " " since our 0528 release.

ES 1.5.3.0 - Implemented workaround in esTransactionScope to get around a bug in the .NET SqlTransaction class in that it only maintains a weak reference to the SqlConnection class. See the release notes
http://www.entityspaces.net/portal/Support/ReleaseNotes/EntitySpaces2007Releasev005280/tabid/174/Default.aspx

Also, from the Wilson OR mapper forums confirming this is a bug in the Microsoft SQL transaction class, read the 2nd post especially.

We encountered the same random Zombie.  A few months ago, we opened a support incident with Microsoft.  After many (many...many...many...) hours of troubleshooting, researching, escalating, and finger pointing, Microsoft finally conceded that this is a bug with ADO.Net.  In specific, it's a bug with the SqlTransaction object.  It will be fixed in a "future version" of ADO.Net, but a hotfix will not be issued since this is an "obscure scenario with a simple workaround."

http://www.ormapper.net/Forums/Default.aspx?part=74&action=thread&id=2450&key=IaC12zYu0ykFv3qP9v11Tg%3D%3D

We implemented the work around and the bug never re-appeared. Could it be that you are accidentally linked with older versions. I really think you need to simplify that connection setup logic so that it is not removing and adding connections unless you are locking and blocking all other DB access.

There really is no logging to detect this.

Thanks for your time.  I discovered that an exception earlier in the transaction was causing this problem to creep up, though I'm not sure how they are related.  The exception was slipping by me due to poor practice that ended like this...

try {
	log.Save();
}
catch { }

It wasn't really important to stop processing if the log entry failed, but no handling code was ever written.  This particular exception was another related to http://community.entityspaces.net/forums/thread/5757.aspx.

Everything is working great now.  Thanks again for your help.