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);
}
