LoadSelect of SoftDelete-able records triggers error with multiple IsDeleted clauses

ServiceStack.OrmLite
1

Hi. I am attempting to use the MSSQL softdelete functionality that you implemented at my request a while back (thanks!) but I am experiencing a crash with error "System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable “@1"” when using the LoadSelect() condition, specifically when querying the referential fields.

I have EventCategoryTbl with each record requiring a linked image FileTbl, both tables are softdeleteable. I initially added the Ormlite interceptor to add the IsDeleted==false condition to all my tables, then swapped it to explicit per-table declaration style while trying to debug this issue.

When I call either:

 var categoryRows = Db.LoadSelect<EventCategoryTbl>();

or (as i saw in one of the sample snippets)

 var categoryRows = Db.LoadSelect<EventCategoryTbl>(x => !x.IsDeleted);

I get errors as per the following (note the multiple, apparently duplicated IsDeleted conditions):

"SELECT \"FileId\", \"Name\", \"Extension\", \"FileSizeBytes\", \"RowVersion\", \"IsDeleted\" 
FROM \"File\" 
WHERE \"FileId\" IN (
	SELECT \"EventCategory\".\"LinkedImageId\" \n
	FROM \"EventCategory\"\n
	WHERE (\"EventCategoryId\" <> @0) AND (\"IsDeleted\" <> @1) AND (\"IsDeleted\" <> @2) AND (\"IsDeleted\" <> @3))"

While trying to debug this, I referred to the GitHub source and attempt to run the tests locally on my machine because I can see here (https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/tests/ServiceStack.OrmLite.Tests/SoftDeleteTests.cs) that you test for this exact situation.

The test case Can_filter_deleted_products_reference_data() runs for me successfully, however, I notice that the test condition is currently set to check that Assert.That(vendors[0].Products.Count, Is.EqualTo(2));", but I think that should be returning only a single record, because one of the Products IS deleted?

So my problem is two-fold - I have a strange condition preventing me from using the softdelete with LoadSelect, and the test case that I am referring to to confirm the functionality doesn’t match my understanding of the functionality.

For reference, the two tables are declared as:

[Alias("EventCategory")]
public class EventCategoryTbl : IHasTimeStamp, IHasSoftDelete
{
	[PrimaryKey]
	public Guid EventCategoryId { get; set; }


	[Required]
	public string Name { get; set; }


	/// <summary>
	/// Link to the file record that contains any image related to this category
	/// </summary>
	[References(typeof(FileTbl))]
	public Guid LinkedImageId { get; set; }

	[Reference]
	public FileTbl LinkedImage { get; set; }


	[Reference]
	public List<EventTbl> Events { get; set; } = new List<EventTbl>();


	public bool IsDeleted { get; set; }

	[RowVersion]
	public ulong RowVersion { get; set; }

}

and

[Alias("File")]
public class FileTbl : IHasTimeStamp, IHasSoftDelete
{
	[PrimaryKey]
	public Guid FileId { get; set; }

	public string Name { get; set; }

	public string Extension { get; set; }

	public long FileSizeBytes { get; set; }

	[RowVersion]
	public ulong RowVersion { get; set; }
	public bool IsDeleted { get; set; }
}
2 
var vendors = db.LoadSelect<Vendor>(x => !x.IsDeleted);

The filter is only applied to the Vendor there’s no way to change the filter used to load its child references, which would need to be done on the client. e.g:

vendors.ForEach(x => x.Products.RemoveAll(p => p.IsDeleted));
3

Ah okay… so the filter is valid only to the "root"queried object (in this case, Vendor / EventCategory, respectively). But I can’t actually use the LoadSelect at all because of the crashing SQL / undeclared parameters? Do you have any ideas what might cause that?

I am currently working around the issue by doing a manual multi-step:

var cats = db.Select<EventCategoryTbl>();
var files = db.Select<FileTbl>(x => cats.Select(f => f.FileId).Contains(x.FileId));
cats.Merge(files);

which works okay, but the LoadSelect is entirely broken.

4

Can you post a complete example (i.e. with all classes/interfaces) I can run, preferably on Gistlyn or a stand-alone unit test.

5

Sorry for the length, I couldn’t see how to get a myget reference on gistlyn, but for a locally hosted mssql server instance i get the same issue (with v5.0 packages from myget):

using System;
using ServiceStack;
using ServiceStack.Data;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.SqlServer;

namespace ConsoleApp1
{
	class Program
	{
		static void Main(string[] args)
		{
			Console.WriteLine("Hello World!");

			// Automatically filter out all soft deleted records, for ALL table types.
			// See https://forums.servicestack.net/t/ormlite-support-for-soft-delete-record-status-flags/3503/3
			OrmLiteConfig.SqlExpressionSelectFilter = q =>
			{
				if (q.ModelDef.ModelType.HasInterface(typeof(IHasSoftDelete)))
				{
					q.Where<IHasSoftDelete>(x => x.IsDeleted != true);
				}
			};

			string connectionString = "Server=localhost;Database=mytempdb;User Id=sa; Password=mytemppassword;";
			IDbConnectionFactory fac = new OrmLiteConnectionFactory(connectionString, SqlServer2012OrmLiteDialectProvider.Instance);

			using (var db = fac.OpenDbConnection())
			{
				db.DropTable<EventCategoryTbl>();
				db.DropTable<FileTbl>();
				
				
				db.CreateTable<FileTbl>();
				db.CreateTable<EventCategoryTbl>();

				var lastSql = "";
				var error = "";
				try
				{
					var cats = db.LoadSelect<EventCategoryTbl>();
				}
				catch (Exception e)
				{
					lastSql = db.GetLastSql();
					error = e.ToString();
				}

				Console.WriteLine(error);
				Console.WriteLine(lastSql);
				Console.WriteLine("  ");
				Console.WriteLine("now i wait for you to type something");
				Console.ReadLine();

			}

		}
	}


	[Alias("EventCategory")]
	public class EventCategoryTbl : IHasSoftDelete, IHasTimeStamp 
	{
		[PrimaryKey]
		public Guid EventCategoryId { get; set; }


		[System.ComponentModel.DataAnnotations.Required]
		public string Name { get; set; }


		/// <summary>
		/// Link to the file record that contains any image related to this category
		/// </summary>
		[References(typeof(FileTbl))]
		public Guid LinkedImageId { get; set; }

		[Reference]
		public FileTbl LinkedImage { get; set; }

		public bool IsDeleted { get; set; }

		[RowVersion]
		public ulong RowVersion { get; set; }
	}


	[Alias("File")]
	public class FileTbl : IHasSoftDelete, IHasTimeStamp
	{
		[PrimaryKey]
		public Guid FileId { get; set; }

		public string Name { get; set; }

		public string Extension { get; set; }

		public long FileSizeBytes { get; set; }

		public bool IsDeleted { get; set; }

		[RowVersion]
		public ulong RowVersion { get; set; }
	}

	public interface IHasTimeStamp
	{
		[RowVersion]
		ulong RowVersion { get; set; }
	}

	public interface IHasSoftDelete
	{
		bool IsDeleted { get; set; }
	}


}
6

Thanks, this should now be resolved with the latest ServiceStack v5 that’s now available on MyGet, if you have an existing v5 installed you’ll need to clear your NuGet cache with:

$ nuget locals all -clear
1 Like