C# OrmLiteConnectionFactory.Select方法代码示例

		public void Transaction_rollsback_transactions_to_different_tables()
		{
            using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<ModelWithIdAndName>(true);
				db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
				db.CreateTable<ModelWithOnlyStringFields>(true);
				db.DeleteAll<ModelWithIdAndName>();
				db.Insert(new ModelWithIdAndName(0));

				using (var dbTrans = db.BeginTransaction())
				{
					db.Insert(new ModelWithIdAndName(0));
					db.Insert(ModelWithFieldsOfDifferentTypes.Create(3));
					db.Insert(ModelWithOnlyStringFields.Create("id3"));

					Assert.That(db.Select<ModelWithIdAndName>(), Has.Count.EqualTo(2));
					Assert.That(db.Select<ModelWithFieldsOfDifferentTypes>(), Has.Count.EqualTo(1));
					Assert.That(db.Select<ModelWithOnlyStringFields>(), Has.Count.EqualTo(1));
				}

				Assert.That(db.Select<ModelWithIdAndName>(), Has.Count.EqualTo(1));
				Assert.That(db.Select<ModelWithFieldsOfDifferentTypes>(), Has.Count.EqualTo(0));
				Assert.That(db.Select<ModelWithOnlyStringFields>(), Has.Count.EqualTo(0));
			}
		}

		public void CascadesOnDelete()
		{
			using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<TypeWithOnDeleteCascade>(true);
				
				db.Save(new ReferencedType { Id = 1 });
				db.Save(new TypeWithOnDeleteCascade { RefId = 1 });
				
				Assert.AreEqual(1, db.Select<ReferencedType>().Count);
				Assert.AreEqual(1, db.Select<TypeWithOnDeleteCascade>().Count);
				
				db.Delete<ReferencedType>(r => r.Id == 1);
				
				Assert.AreEqual(0, db.Select<ReferencedType>().Count);
				Assert.AreEqual(0, db.Select<TypeWithOnDeleteCascade>().Count);
			}
		}

		public void Transaction_rollsback_if_not_committed()
		{
			using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<ModelWithIdAndName>(true);
				db.DeleteAll<ModelWithIdAndName>();
				db.Insert(new ModelWithIdAndName(0));

				using (var dbTrans = db.BeginTransaction())
				{
					db.Insert(new ModelWithIdAndName(0));
					db.Insert(new ModelWithIdAndName(0));

					var rowsInTrans = db.Select<ModelWithIdAndName>();
					Assert.That(rowsInTrans, Has.Count.EqualTo(3));
				}

				var rows = db.Select<ModelWithIdAndName>();
				Assert.That(rows, Has.Count.EqualTo(1));
			}
		}

		public void Can_query_using_float_in_alernate_culuture()
		{
            using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<Point>(true);

				db.Insert(new Point { Width = 4, Height = 1.123f, Top = 3.456d, Left = 2.345m});
								
				var points = db.Select<Point>();

				Console.WriteLine(points.Dump());

				Assert.That(points[0].Width, Is.EqualTo(4));
				Assert.That(points[0].Height, Is.EqualTo(1.123f));
				Assert.That(points[0].Top, Is.EqualTo(3.456d));
				Assert.That(points[0].Left, Is.EqualTo(2.345m));
				
				points = db.Select<Point>("Height={0}", 1.123f);  // returns no rows! FirebirdSql bug?
				
				Assert.That(points.Count>0);
			}
		}

		public void Can_insert_and_select_from_OrderLineData()
		{
            using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<SampleOrderLine>(true);

				var orderIds = new[] { 1, 2, 3, 4, 5 }.ToList();

				orderIds.ForEach(x => db.Insert(
					SampleOrderLine.Create(Guid.NewGuid(), x, 1)));

				var rows = db.Select<SampleOrderLine>();
				Assert.That(rows, Has.Count.EqualTo(orderIds.Count));
			}
		}

		public void Can_preserve_ModelWithIdOnly_table()
		{
            using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<ModelWithIdOnly>(true);

				db.Insert(new ModelWithIdOnly(1));
				db.Insert(new ModelWithIdOnly(2));

				db.CreateTable<ModelWithIdOnly>(false);

				var rows = db.Select<ModelWithIdOnly>();

				Assert.That(rows, Has.Count.EqualTo(2));
			}
		}

		public void Can_SaveAll_and_select_from_ModelWithFieldsOfDifferentTypes_table_with_no_ids()
		{
			using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
				db.DeleteAll<ModelWithFieldsOfDifferentTypes>();
				var rowIds = new List<int> {1, 2, 3, 4, 5};
				var newRows = rowIds.ConvertAll(x => ModelWithFieldsOfDifferentTypes.Create(default(int)));

				db.SaveAll(newRows);

				var rows = db.Select<ModelWithFieldsOfDifferentTypes>();

				Assert.That(rows, Has.Count.EqualTo(newRows.Count));
			}
		}

		public void Can_insert_and_select_from_ModelWithFieldsOfNullableTypes_table()
		{
            using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<ModelWithFieldsOfNullableTypes>(true);

				var row = ModelWithFieldsOfNullableTypes.Create(1);

				db.Insert(row);

				var rows = db.Select<ModelWithFieldsOfNullableTypes>();

				Assert.That(rows, Has.Count.EqualTo(1));

				ModelWithFieldsOfNullableTypes.AssertIsEqual(rows[0], row);
			}
		}

		public void Can_Delete_from_ModelWithFieldsOfDifferentTypes_table()
		{
			using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);

				var rowIds = new List<int>(new[] {1, 2, 3});
				rowIds.ForEach(x => db.Insert(ModelWithFieldsOfDifferentTypes.Create(x)));

				var rows = db.Select<ModelWithFieldsOfDifferentTypes>();
				var row2 = rows.First(x => x.Id == 2);

				db.Delete(row2);

				rows = db.GetByIds<ModelWithFieldsOfDifferentTypes>(rowIds);
				var dbRowIds = rows.ConvertAll(x => x.Id);

				Assert.That(dbRowIds, Is.EquivalentTo(new[] {1, 3}));
			}
		}

		public void Can_select_with_filter_from_ModelWithOnlyStringFields_table()
		{
			using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<ModelWithOnlyStringFields>(true);

				var rowIds = new List<string>(new[] { "id-1", "id-2", "id-3" });

				rowIds.ForEach(x => db.Insert(ModelWithOnlyStringFields.Create(x)));

				var filterRow = ModelWithOnlyStringFields.Create("id-4");
				filterRow.AlbumName = "FilteredName";

				db.Insert(filterRow);

				var rows = db.Where<ModelWithOnlyStringFields>(new { filterRow.AlbumName });
				var dbRowIds = rows.ConvertAll(x => x.Id);
				Assert.That(dbRowIds, Has.Count.EqualTo(1));
				Assert.That(dbRowIds[0], Is.EqualTo(filterRow.Id));

				rows = db.Where<ModelWithOnlyStringFields>(new { filterRow.AlbumName });
				dbRowIds = rows.ConvertAll(x => x.Id);
				Assert.That(dbRowIds, Has.Count.EqualTo(1));
				Assert.That(dbRowIds[0], Is.EqualTo(filterRow.Id));

				var queryByExample = new ModelWithOnlyStringFields { AlbumName = filterRow.AlbumName };
				rows = db.SelectNonDefaults(queryByExample);
				dbRowIds = rows.ConvertAll(x => x.Id);
				Assert.That(dbRowIds, Has.Count.EqualTo(1));
				Assert.That(dbRowIds[0], Is.EqualTo(filterRow.Id));

				rows = db.Select<ModelWithOnlyStringFields>(
					"SELECT * FROM ModelWithOnlyStringFields WHERE AlbumName = @AlbumName", new { filterRow.AlbumName });
				dbRowIds = rows.ConvertAll(x => x.Id);
				Assert.That(dbRowIds, Has.Count.EqualTo(1));
				Assert.That(dbRowIds[0], Is.EqualTo(filterRow.Id));
			}
		}

		public void Can_create_multiple_tables()
		{
            using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTables(true, typeof(ModelWithIdOnly), typeof(ModelWithIdAndName));

				db.Insert(new ModelWithIdOnly(1));
				db.Insert(new ModelWithIdOnly(2));

				db.Insert(new ModelWithIdAndName(0));
				db.Insert(new ModelWithIdAndName(0));

				var rows1 = db.Select<ModelWithIdOnly>();
				var rows2 = db.Select<ModelWithIdOnly>();

				Assert.That(rows1, Has.Count.EqualTo(2));
				Assert.That(rows2, Has.Count.EqualTo(2));
			}
		}

		public void Can_SaveAll_and_select_from_Movie_table()
		{
			using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<Movie>(true);

				var top5Movies = new List<Movie>
					                 {
						                 new Movie
							                 {
								                 Id = "tt0111161",
								                 Title = "The Shawshank Redemption",
								                 Rating = 9.2m,
								                 Director = "Frank Darabont",
								                 ReleaseDate = new DateTime(1995, 2, 17),
								                 TagLine = "Fear can hold you prisoner. Hope can set you free.",
								                 Genres = new List<string> {"Crime", "Drama"},
							                 },
						                 new Movie
							                 {
								                 Id = "tt0068646",
								                 Title = "The Godfather",
								                 Rating = 9.2m,
								                 Director = "Francis Ford Coppola",
								                 ReleaseDate = new DateTime(1972, 3, 24),
								                 TagLine = "An offer you can't refuse.",
								                 Genres = new List<string> {"Crime", "Drama", "Thriller"},
							                 },
						                 new Movie
							                 {
								                 Id = "tt1375666",
								                 Title = "Inception",
								                 Rating = 9.2m,
								                 Director = "Christopher Nolan",
								                 ReleaseDate = new DateTime(2010, 7, 16),
								                 TagLine = "Your mind is the scene of the crime",
								                 Genres = new List<string> {"Action", "Mystery", "Sci-Fi", "Thriller"},
							                 },
						                 new Movie
							                 {
								                 Id = "tt0071562",
								                 Title = "The Godfather: Part II",
								                 Rating = 9.0m,
								                 Director = "Francis Ford Coppola",
								                 ReleaseDate = new DateTime(1974, 12, 20),
								                 Genres = new List<string> {"Crime", "Drama", "Thriller"},
							                 },
						                 new Movie
							                 {
								                 Id = "tt0060196",
								                 Title = "The Good, the Bad and the Ugly",
								                 Rating = 9.0m,
								                 Director = "Sergio Leone",
								                 ReleaseDate = new DateTime(1967, 12, 29),
								                 TagLine = "They formed an alliance of hate to steal a fortune in dead man's gold",
								                 Genres = new List<string> {"Adventure", "Western"},
							                 },
					                 };

				db.SaveAll(top5Movies);

				var rows = db.Select<Movie>();

				Assert.That(rows, Has.Count.EqualTo(top5Movies.Count));
			}
		}

		public void Can_Select_subset_ModelWithIdAndName_from_ModelWithFieldsOfDifferentTypes_table()
		{
			using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);

				var rowIds = new List<int>(new[] {1, 2, 3});

				rowIds.ForEach(x => db.Insert(ModelWithFieldsOfDifferentTypes.Create(x)));

				var rows = db.Select<ModelWithIdAndName>("SELECT Id, Name FROM ModelWFDT");
				var dbRowIds = rows.ConvertAll(x => x.Id);

				Assert.That(dbRowIds, Is.EquivalentTo(rowIds));
			}
		}

		public void Can_insert_table_with_UserAuth()
		{
            using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				db.CreateTable<UserAuth>(true);
				
				var jsv = "{Id:0,UserName:UserName,Email:[email protected],PrimaryEmail:[email protected],FirstName:FirstName,LastName:LastName,DisplayName:DisplayName,Salt:WMQi/g==,PasswordHash:oGdE40yKOprIgbXQzEMSYZe3vRCRlKGuqX2i045vx50=,Roles:[],Permissions:[],CreatedDate:2012-03-20T07:53:48.8720739Z,ModifiedDate:2012-03-20T07:53:48.8720739Z}";
				var userAuth = jsv.To<UserAuth>();

				db.Insert(userAuth);

				var rows = db.Select<UserAuth>(q => q.UserName == "UserName");

				Console.WriteLine(rows[0].Dump());

				Assert.That(rows[0].UserName, Is.EqualTo(userAuth.UserName));
			}
		}

		public void Can_insert_table_with_blobs()
		{
            using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
			{
				var hold = OrmLiteConfig.DialectProvider.GetStringConverter().StringLength;
				OrmLiteConfig.DialectProvider.GetStringConverter().StringLength = 1024;
				
				db.CreateTable<OrderBlob>(true);
				OrmLiteConfig.DialectProvider.GetStringConverter().StringLength = hold;

				var row = OrderBlob.Create(1);

				db.Insert(row);

				var rows = db.Select<OrderBlob>();

				Assert.That(rows, Has.Count.EqualTo(1));

				var newRow = rows[0];

				Assert.That(newRow.Id, Is.EqualTo(row.Id));
				Assert.That(newRow.Customer.Id, Is.EqualTo(row.Customer.Id));
				Assert.That(newRow.Employee.Id, Is.EqualTo(row.Employee.Id));
				Assert.That(newRow.IntIds, Is.EquivalentTo(row.IntIds));
				Assert.That(newRow.CharMap, Is.EquivalentTo(row.CharMap));
				Assert.That(newRow.OrderDetails.Count, Is.EqualTo(row.OrderDetails.Count));
				Assert.That(newRow.OrderDetails[0].ProductId, Is.EqualTo(row.OrderDetails[0].ProductId));
				Assert.That(newRow.OrderDetails[1].ProductId, Is.EqualTo(row.OrderDetails[1].ProductId));
				Assert.That(newRow.OrderDetails[2].ProductId, Is.EqualTo(row.OrderDetails[2].ProductId));
			}
		}