SaveAll to set [AutoIncrement] or [AutoId]?

ServiceStack.OrmLite
1

Often we want to absorb records (let’s say from an import of data) into a SQL Server db.

Some records in the import will have Ids and will already exist in the database. Some are new. Let’s say there are 1000 records.

I was doing some reading in a competing library where their method to batch insert actually returns the identities for new records.

Is there any way to do this with OrmLite’s SaveAll method?

Right now we have logic that tries to do this manually in a non-performant way:

  1. Find all records that do already exist, and manually create SQL update strings and batch call them to update the data.
  2. Then loop 1 by 1 and call Save on each item to ensure it is inserted and the identity Id is returned.

There must be a better way that OrmLite can better help facilitate this?

2

The Save* APIs should already populate any Auto Increment Ids on the Id property.

3

It works if I send a List through, but to test I was sending an IEnumerable and the Ids were not set.

To test this:

public class Product {
		private int _Id;
		private DateTime _CreatedDate;
		private DateTime _UpdatedDate;
		private int _CreatedByPortalUserId;
		private int _UpdatedByPortalUserId;
		private string _Notes;

		// SQL Type: int
		[Alias("ProductId")]
		[AutoIncrement]
		[JsonProperty]
		public int Id { 
			get => _Id;
			set {
				_Id = value;
			}
		}
		// SQL Type: datetime2
		[Required]
		[JsonProperty]
		public DateTime CreatedDate { 
			get { 
				return _CreatedDate; 
			}
			set {
				_CreatedDate = value;
			}

		}
		// SQL Type: datetime2
		[Required]
		[JsonProperty]
		public DateTime UpdatedDate { 
			get { 
				return _UpdatedDate; 
			}
			set {
				_UpdatedDate = value;
			}

		}
		// SQL Type: int
		[Required]
		[JsonProperty]
		public int CreatedByPortalUserId { 
			get { 
				return _CreatedByPortalUserId; 
			}
			set {
				_CreatedByPortalUserId = value;
			}

		}
		// SQL Type: int
		[Required]
		[JsonProperty]
		public int UpdatedByPortalUserId { 
			get { 
				return _UpdatedByPortalUserId; 
			}
			set {
				_UpdatedByPortalUserId = value;
			}

		}

		// SQL Type: nvarchar
		[Required]
		[JsonProperty]
		public string Notes { 
			get { 
				return _Notes; 
			}
			set {
				_Notes = value;
			}

		}
}

        private IEnumerable<Product> GetProducts(int count = 5)
        {
            for (int i = 0; i < count; i++)
            {
                yield return new Product
                {
                    CreatedDate = DateTime.Now,
                    Notes = $"{i}",
                    UpdatedDate = DateTime.Now,
                    CreatedByPortalUserId = 1,
                    UpdatedByPortalUserId = 1
                };
            }
        }

            var products = GetProducts().ToList(); // WITHOUT ToList() the Id is not set

            await db.SaveAllAsync<Product>(products);

            Debug.Assert(products.All(x => x.Id > 0));
4

Did you try to debug this? The instances are getting populated, but you’re not seeing them because you’re not looking at the same instances, you’re just re-enumerating the yielded sequence again, creating & inspecting new instances.