public void SQLWithOrderByWithoutFrom()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID).OrderBy(c.Country)
,
@"SELECT CustomerId FROM Customers ORDER BY Country;"
);
}
public void SelectFromSelect_1()
{
var c = new Models.Customers();
Verify(
Select(c.Country).From(Select(Distinct(c.Country)).From(c))
,
@"SELECT Country AS DistinctCountries FROM (SELECT DISTINCT Country FROM Customers) x"
);
}
public void BasicSelectStatement_from_is_not_required()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID, c.CompanyName)
,
"select customerid,companyName from customers"
);
}
public void SQLWithNot()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID).Where(Not(c.Country.IsEqualTo("Germany")))
,
@"SELECT CustomerId FROM Customers WHERE NOT Country='Germany'"
);
}
public void SQLWithDistinct()
{
var c = new Models.Customers();
Verify(
Select(Distinct(c.Country)).From(c)
,
"SELECT DISTINCT Country FROM Customers"
);
}
public void SQLWithNotIn()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID).Where(c.Country.IsNotIn("Germany", "France", "UK"))
,
@"SELECT CustomerID FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');"
);
}
public void SQLWith()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID)
,
@"SELECT CustomerId FROM Customers "
);
}
public void SQLWithCountDistinct()
{
var c = new Models.Customers();
Verify(
Select(Count(Distinct(c.Country))).From(c)
,
@"SELECT COUNT(DISTINCT Country) FROM Customers;
"
);
}
public void BasicSelectStatement()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID, c.CompanyName)
.From(c)
,
"select customerid,companyName from customers"
);
}
public void SQLWithLike()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID).From(c).
Where(Like(c.CompanyName, "%or%"))
,
@"SELECT customerid FROM Customers
WHERE companyname LIKE '%or%'"
);
}
public void SQLWithOrderByAsc()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID).From(c).
Where(c.Country.IsEqualTo("Germany")).OrderBy(c.CustomerID)
,
@"SELECT CustomerID FROM Customers
WHERE Country='Germany' order by CustomerID asc"
);
}
public void SQLWithOr()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID).From(c).
Where(c.City.IsEqualTo("Berlin").Or(c.City.IsEqualTo("München")))
,
@"SELECT CustomerID FROM Customers
WHERE City='Berlin' OR City='München' "
);
}
public void SQLWithOrderByDescAsc()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID).From(c).
Where(c.Country.IsEqualTo("Germany")).OrderBy(c.Country, c.CompanyName, SortDirection.Descending)
,
@"SELECT CustomerID FROM Customers
WHERE Country='Germany' order by Country ASC, CompanyName DESC"
);
}
public void SQLWithGroupByWithoutWhere()
{
var c = new Models.Customers();
Verify(
Select(Count(c.CustomerID), c.Country).GroupBy(c.Country)
,
@"SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;"
);
}
public void SQLWithAnd()
{
var c = new Models.Customers();
Verify(
Select(c.CustomerID).From(c).
Where(c.Country.IsEqualTo("Germany").And(c.City.IsEqualTo("Berlin")))
,
@"SELECT CustomerID FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin'; "
);
}
public void TestListOfSelectItems()
{
var c = new Models.Customers();
var columns = new SelectItems {
c.CustomerID, c.CompanyName
};
Verify(
Select(columns, c.Phone)
,
"select customerid,companyName,phone from customers"
);
}
public void SQLWithIsNotNull()
{
var c = new Models.Customers();
Verify(
Select(c.CompanyName, c.ContactName, c.Address).From(c).
Where(c.Address.IsDifferentFrom((Text)null))
,
@"SELECT CompanyName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL"
);
}
public void SQLWithGroupBy()
{
var c = new Models.Customers();
Verify(
Select(Count(c.CustomerID), c.Country).Where(Like(c.CompanyName, "%an%")).GroupBy(c.Country)
,
@"SELECT COUNT(CustomerID), Country
FROM Customers
WHERE CompanyName LIKE '%an%'
GROUP BY Country;"
);
}
public void SQLWithLikeUsingUserDbMethods()
{
var c = new Models.Customers();
var db = new ENV.Data.UserDbMethods(() => c);
Verify(
Select(c.CustomerID).From(c).
Where(db.Like(c.CompanyName, "*or*"))
,
@"SELECT customerid FROM Customers
WHERE companyname LIKE '%or%'"
);
}
public void SQLWithUnionAll()
{
var o = new Models.Orders();
var c = new Models.Customers();
Verify(
Select(c.City, c.Country).Where(c.Country.IsEqualTo("Germany")).UnionAll(
Select(o.ShipCity, o.ShipCountry).Where(o.ShipCountry.IsEqualTo("Germany"))).OrderBy(c.City)
,
@"SELECT City, Country FROM Customers WHERE Country='Germany'
UNION ALL
SELECT ShipCity, ShipCountry FROM orders WHERE ShipCountry='Germany'
ORDER BY City;"
);
}
public void SQLWithInnerJoins()
{
var o = new Models.Orders();
var c = new Models.Customers();
Verify(
Select(o.OrderID, c.CompanyName, o.OrderDate).
From(o).
InnerJoin(c, o.CustomerID.IsEqualTo(c.CustomerID))
,
@"SELECT Orders.OrderID, Customers.CompanyName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID "
);
}
public void SQLWithHavingCount()
{
var c = new Models.Customers();
Verify(
Select(c.City)
.GroupBy(c.City)
.Having(new SqlPart(Count(), " = ", 1))
,
@"select city
from customers
group by city
having count(*)=1"
);
}
public void SQLWithLamda_1()
{
var u = new ENV.UserMethods();
var o = new Models.Orders();
var c = new Models.Customers();
Verify(
Select(o.OrderID, c.CompanyName, o.OrderDate).
From(o).
InnerJoin(c, new SqlPart(o.CustomerID, "=", new SqlFunction("Left", c.CustomerID, 5)))
,
@"SELECT Orders.OrderID, Customers.CompanyName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = left(Customers.CustomerID,5) "
);
}
public void SQLWithHaving()
{
var c = new Models.Customers();
Verify(
Select(Count(c.CustomerID), c.Country)
.GroupBy(c.Country)
.Having(new SqlPart(Count(c.CustomerID), " > ", 5))
,
@"SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5"
);
}
public void SQLWithHavingOrderBy()
{
var c = new Models.Customers();
Verify(
Select(Count(c.CustomerID), c.Country)
.GroupBy(c.Country)
.Having(new SqlPart(Count(c.CustomerID), " > ", 5))
.OrderBy(Count(c.CustomerID), SortDirection.Descending)
,
@"SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC"
);
}
public void SQLWithUnion()
{
var o = new Models.Orders();
var c = new Models.Customers();
Verify(
Select(c.City, c.Country).Where(c.Country.IsEqualTo("Germany")).Union(
Select(o.ShipCity, o.ShipCountry).Where(o.ShipCountry.IsEqualTo("Germany"))).OrderBy(1, SortDirection.Descending)
,
@"SELECT City, Country FROM Customers WHERE Country='Germany'
UNION
SELECT ShipCity, ShipCountry FROM orders WHERE ShipCountry='Germany'
ORDER BY City desc;"
);
}
public void SQLWithRightJoin()
{
var o = new Models.Orders();
var c = new Models.Customers();
Verify(
Select(c.CompanyName, o.OrderID).
From(c).
RightOuterJoin(o, o.CustomerID.IsEqualTo(c.CustomerID)).
OrderBy(c.CompanyName)
,
@"SELECT Customers.CompanyName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CompanyName "
);
}
public void SQLWithFullJoin()
{
var o = new Models.Orders();
var c = new Models.Customers();
Verify(
Select(c.CompanyName, o.OrderID).
From(c).
FullOuterJoin(o, c.CustomerID.IsEqualTo(o.CustomerID)).
OrderBy(c.CompanyName)
,
@"SELECT Customers.CompanyName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CompanyName "
);
}
public void SQLWithCase2()
{
var c = new Models.Customers();
Verify(
Select(c.CompanyName,
c.City, c.Country).OrderBy(
Case(c.City.IsNull(), c.Country).Else(c.City)
)
,
@"SELECT CompanyName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END)"
);
}
public void AddCustomer(Customers customer)
{
context.Customers.Add(customer);
context.SaveChanges();
}