public void SimpleExceptTest() { Require.AllFeaturesSupported(ProviderFeatures.TemporaryTables); Require.ProviderIsNot(StorageProvider.SqlServerCe); Require.ProviderIsNot(StorageProvider.MySql); var tracks = Session.Query.All <Track>(); var customers = Session.Query.All <Customer>(); var trackFirstChars = tracks.Select(t => t.Name.Substring(0, 1)); var customerFirstChars = customers.Select(c => c.FirstName.Substring(0, 1)).ToList(); var trackOnlyFirstChars = trackFirstChars.Except(customerFirstChars); Assert.That(trackOnlyFirstChars, Is.Not.Empty); QueryDumper.Dump(trackOnlyFirstChars); }
public void ArrayContainsTest() { var list = new[] { "Michelle", "Jack" }; var query = from c in Session.Query.All <Customer>() where !list.Contains(c.FirstName) select c.Invoices; var expected = from c in Customers where !list.Contains(c.FirstName) select c.Invoices; Assert.That(query, Is.Not.Empty); Assert.AreEqual(0, expected.Except(query).Count()); QueryDumper.Dump(query); }
public void AllTest() { Require.AllFeaturesSupported(ProviderFeatures.TemporaryTables); var localInvoiceCommissions = Session.Query.All <Invoice>().Select(invoice => invoice.Commission).Take(5).ToList(); var query = Session.Query.All <Invoice>() .Where(invoice => localInvoiceCommissions.All(commission => commission != invoice.Commission)); Assert.That(query, Is.Not.Empty); QueryDumper.Dump(query); var expectedQuery = Invoices .Where(invoice => localInvoiceCommissions.All(commission => commission != invoice.Commission)); Assert.AreEqual(0, expectedQuery.Except(query).Count()); }
public void QueryableEntityContainsTest() { var list = Session.Query.All <Customer>().Take(5); var query = from c in Session.Query.All <Customer>() where !c.In(list) select c.Invoices; var expected = from c in Session.Query.All <Customer>().AsEnumerable() where !c.In(list) select c.Invoices; Assert.That(query, Is.Not.Empty); Assert.AreEqual(0, expected.Except(query).Count()); QueryDumper.Dump(query); }
public void ComplexCondition2Test() { var includeAlgorithm = IncludeAlgorithm.TemporaryTable; var query = from track in Session.Query.All <Track>() where track.Milliseconds.In(includeAlgorithm, 276192, 349492, 232463) select track; var expected = from track in Session.Query.All <Track>().AsEnumerable() where track.Milliseconds.In(includeAlgorithm, 276192, 349492, 232463) select track; Assert.That(query, Is.Not.Empty); Assert.AreEqual(0, expected.Except(query).Count()); QueryDumper.Dump(query); }
public void UnionAnonymous2Test() { // SQLite does not support paging operations inside set operations Require.ProviderIsNot(StorageProvider.Sqlite); var customers = Session.Query.All <Customer>(); var result = customers.Select(c => new { Company = c.CompanyName, c.LastName, c.Address }) .Where(c => c.Address.StreetAddress.Length < 10) .Select(c => new { c.Company, c.Address.City }) .Take(10) .Union(customers.Select(c => new { Company = c.CompanyName, c.Address.City })).Where(c => c.Company.Length < 10); QueryDumper.Dump(result); }
public void StructContainsTest() { var list = Session.Query.All <Customer>().Take(5).Select(c => c.Address).ToList(); var query = from c in Session.Query.All <Customer>() where !c.Address.In(list) select c.Invoices; var expected = from c in Customers where !c.Address.In(list) select c.Invoices; Assert.That(query, Is.Not.Empty); Assert.AreEqual(0, expected.Except(query).Count()); QueryDumper.Dump(query); }
public void UnionDifferentTest() { var customers = Session.Query.All <Customer>(); var employees = Session.Query.All <Employee>(); var result = ( from c in customers select c.Address.Country ).Union( from e in employees select e.Address.Country ); QueryDumper.Dump(result); }
public void ConcatDifferentTest2() { var customers = Session.Query.All <Customer>(); var employees = Session.Query.All <Employee>(); var result = ( from c in customers select new { Name = c.CompanyName, c.Phone } ).Concat( from e in employees select new { Name = e.FirstName + " " + e.LastName, Phone = e.Phone } ); QueryDumper.Dump(result); }
public void GroupByWithSelectorSelectManyTest() { var result = Session.Query.All <InvoiceLine>() .GroupBy(c => c.Track.Name, (trackName, invoiceLines) => invoiceLines.Where(k => k.Invoice.Customer.FirstName.Substring(0, 1) == trackName.Substring(0, 1))) .SelectMany(k => k); var expected = InvoiceLines .GroupBy(c => c.Track.Name, (trackName, invoiceLines) => invoiceLines.Where(k => k.Invoice.Customer.FirstName.Substring(0, 1) == trackName.Substring(0, 1))) .SelectMany(k => k); Assert.AreEqual(0, expected.Except(result).Count()); QueryDumper.Dump(result); }
public void ExceptDifferentTest() { Require.AllFeaturesSupported(ProviderFeatures.TemporaryTables); Require.ProviderIsNot(StorageProvider.SqlServerCe); Require.ProviderIsNot(StorageProvider.MySql); var customers = Session.Query.All <Customer>(); var employees = Session.Query.All <Employee>(); var result = customers .Select(c => c.Address.Country) .Except(employees.ToList().Select(e => e.Address.Country)); Assert.That(result, Is.Not.Empty); QueryDumper.Dump(result); }
public void GroupByWithResultSelectorTest4Test() { Require.AllFeaturesSupported(ProviderFeatures.ScalarSubqueries); var result = Session.Query.All <Invoice>().GroupBy(i => i.Customer, (c, g) => new { // Customer = c, Sum = g.Sum(i => i.Commission), Min = g.Min(i => i.Commission), Max = g.Max(i => i.Commission), Avg = g.Average(i => i.Commission) }); Assert.That(result, Is.Not.Empty); QueryDumper.Dump(result); }
public void OuterJoinValueTest() { Require.AllFeaturesSupported(ProviderFeatures.ScalarSubqueries); var assertCount = Session.Query.All <Invoice>().Count() + Session.Query.All <Customer>().Count(c => !Session.Query.All <Invoice>().Any(i => i.Customer == c)); var result = from c in Session.Query.All <Customer>() from i in Session.Query.All <Invoice>().Where(i => i.Customer == c).Select(i => i.PaymentDate).DefaultIfEmpty() select new { c.LastName, i }; var list = result.ToList(); Assert.That(list, Is.Not.Empty); Assert.AreEqual(assertCount, list.Count); QueryDumper.Dump(list); }
public void ArrayAggregateAccessTest() { var result = Session.Query.All <Customer>() .Select(x => new byte[] { 1, 2 }) .Select(a => a[0]) .Sum(b => b); var expected = Session.Query.All <Customer>() .ToList() .Select(x => new byte[] { 1, 2 }) .Select(a => a[0]) .Sum(b => b); Assert.AreEqual(expected, result); QueryDumper.Dump(result); }
public void SubqueryCalculableColumnTest() { Require.ProviderIsNot(StorageProvider.SqlServerCe); var result = Session.Query.All <Invoice>() .Select(invoice => Session.Query.All <InvoiceLine>() .Where(p => p.Invoice == invoice) .Count()); var expectedResult = Invoices .Select(invoice => InvoiceLines .Where(p => p.Invoice == invoice) .Count()); Assert.AreEqual(0, expectedResult.Except(result).Count()); QueryDumper.Dump(result); }
public void DistinctOrderByTest() { var result = Session.Query.All <Customer>() .Select(c => c.Address.City) .Distinct() .OrderBy(c => c); var expected = Session.Query.All <Customer>() .ToList() .Select(c => c.Address.City) .Distinct() .OrderBy(c => c); Assert.IsTrue(expected.SequenceEqual(result)); QueryDumper.Dump(result); }
public void CountAfterFilterTest() { Require.ProviderIsNot(StorageProvider.SqlServerCe | StorageProvider.Oracle | StorageProvider.Sqlite); var q = Session.Query; var result = q.All <Customer>().Where(c => q.All <Invoice>().Where(o => o.Customer == c).Count() > 6); var expected = Customers.Where(c => Invoices.Where(o => o.Customer == c).Count() > 6); var list = result.ToList(); Assert.AreEqual(0, expected.Except(list).Count()); QueryDumper.Dump(result); Assert.Greater(list.Count, 0); }
public void ComplexSubqueryTest() { Require.ProviderIsNot(StorageProvider.SqlServerCe | StorageProvider.MySql); var result = Session.Query.All <Customer>() .Take(2) .Select(c => Session.Query.All <Invoice>() .Select(i => Session.Query.All <Employee>() .Take(2) .Where(e => e.Invoices.Contains(i))) .Where(i => i.Count() > 0)) .Select(qqe => qqe); Assert.That(result, Is.Not.Empty); QueryDumper.Dump(result); }
public void UnionAnonymousCollationsTest() { // SQLite does not support paging operations inside set operations Require.ProviderIsNot(StorageProvider.Sqlite); Require.AllFeaturesSupported(ProviderFeatures.TemporaryTables); var customers = Session.Query.All <Customer>(); var result = customers.Select(c => new { c.FirstName, c.LastName }) .Take(10) .Union(customers.ToList().Select(c => new { c.FirstName, c.LastName })); Assert.That(result, Is.Not.Empty); QueryDumper.Dump(result); }
public void Aggregate2Test() { Require.AllFeaturesSupported(ProviderFeatures.TemporaryTables); Require.ProviderIsNot(StorageProvider.SqlServerCe); var localItems = GetLocalItems(100); var queryable = Session.Query.Store(localItems); var result = Session.Query.All <Invoice>() .Where(invoice => invoice.Commission > queryable.Max(poco => poco.Value2)); var expected = Invoices .Where(invoice => invoice.Commission > localItems.Max(poco => poco.Value2)); Assert.That(result, Is.Not.Empty); Assert.AreEqual(0, expected.Except(result).Count()); QueryDumper.Dump(result); }
public void Poco4Test() { var query = Session.Query.All <Customer>() .Select(customer => new Poco <string, string>(customer.LastName, customer.LastName)) .Select(poco => new { poco.Value1, poco.Value2 }); var expected = Customers .Select(customer => new Poco <string, string> { Value1 = customer.LastName, Value2 = customer.LastName }) .Select(poco => new { poco.Value1, poco.Value2 }); Assert.That(query, Is.Not.Empty); Assert.AreEqual(0, expected.Except(query).Count()); QueryDumper.Dump(query); }
public void ComplexAsCast2Test() { var result = Session.Query.All <Track>() .Select(track => new { VideoTrack = track, AudioTrack = track }) .Select(anonymousArgument => new { AQ = anonymousArgument.AudioTrack as AudioTrack, DQ = anonymousArgument.VideoTrack as VideoTrack }); QueryDumper.Dump(result); }
public void SimpleUnionTest() { var products = Session.Query.All <Track>(); var customers = Session.Query.All <Customer>(); var productFirstChars = from p in products select p.Name.Substring(0, 1); var customerFirstChars = from c in customers select c.CompanyName.Substring(0, 1); var uniqueFirstChars = productFirstChars.Union(customerFirstChars); QueryDumper.Dump(uniqueFirstChars); }
public void UnionAnonymous3Test() { // SQLite does not support paging operations inside set operations Require.ProviderIsNot(StorageProvider.Sqlite); var customers = Session.Query.All <Customer>(); var shipper = Session.Query.All <Employee>(); var result = customers.Select(c => new { c.FirstName, c.LastName, c.Address }) .Where(c => c.Address.StreetAddress.Length < 15) .Select(c => new { Name = c.FirstName, Address = c.Address.City }) .Take(10) .Union(shipper.Select(s => new { Name = s.FirstName, Address = s.Phone })) .Where(c => c.Address.Length < 7); QueryDumper.Dump(result); }
public void SimpleIntContainsTest() { var list = new List <int> { 276192, 349492, 232463 }; var query = from track in Session.Query.All <Track>() where track.Milliseconds.In(list) select track; var expected = from track in Session.Query.All <Track>().AsEnumerable() where track.Milliseconds.In(list) select track; Assert.That(query, Is.Not.Empty); Assert.AreEqual(0, expected.Except(query).Count()); QueryDumper.Dump(query); }
public void GroupWithJoinTest() { Require.ProviderIsNot(StorageProvider.SqlServerCe | StorageProvider.Oracle); var query = Session.Query.All <Customer>() .GroupBy(c => c.Address.Country) .Join(Session.Query.All <Customer>(), country => country.Key, c2 => c2.Address.Country, (country, c2) => new { country = country.Key, total = c2.Invoices.Sum(i => i.Commission) }); Assert.That(query, Is.Not.Empty); QueryDumper.Dump(query); }
public void GroupBySumMinMaxAvgTest() { Require.AllFeaturesSupported(ProviderFeatures.ScalarSubqueries); var result = Session.Query.All <Invoice>() .GroupBy(i => i.Customer) .Select(g => new { Sum = g.Sum(o => o.Commission), Min = g.Min(o => o.Commission), Max = g.Max(o => o.Commission), Avg = g.Average(o => o.Commission) }); Assert.That(result, Is.Not.Empty); QueryDumper.Dump(result); }
public void IListContainsTest() { var list = (IList <string>) new List <string> { "Michelle", "Jack" }; var query = from c in Session.Query.All <Customer>() where !list.Contains(c.FirstName) select c.Invoices; var expected = from c in Session.Query.All <Customer>().AsEnumerable() where !list.Contains(c.FirstName) select c.Invoices; Assert.That(query, Is.Not.Empty); Assert.AreEqual(0, expected.Except(query).Count()); QueryDumper.Dump(query); }
public void Pair2Test() { Require.AllFeaturesSupported(ProviderFeatures.TemporaryTables); Require.AllFeaturesSupported(ProviderFeatures.ScalarSubqueries); var pairs = Session.Query.All <Customer>() .Select(customer => new Pair <string, int>(customer.LastName, (int)customer.Invoices.Count)) .ToList(); var query = Session.Query.All <Customer>() .Join(pairs, customer => customer.LastName, pair => pair.First, (customer, pair) => pair.Second); var expected = Session.Query.All <Customer>().AsEnumerable() .Join(pairs, customer => customer.LastName, pair => pair.First, (customer, pair) => pair.Second); Assert.That(query, Is.Not.Empty); Assert.AreEqual(0, expected.Except(query).Count()); QueryDumper.Dump(query); }
public void ExceptDifferentTest() { Require.ProviderIsNot(StorageProvider.SqlServerCe); Require.ProviderIsNot(StorageProvider.Firebird); Require.ProviderIsNot(StorageProvider.MySql); var customers = Session.Query.All <Customer>(); var employees = Session.Query.All <Employee>(); var result = ( from c in customers select c.Address.Country ).Except( from e in employees select e.Address.Country ); QueryDumper.Dump(result); }