Aliases allow you to provide short reference names for objects (in the case of Xml) and inheritance trees (in the case of attributes). They can be combined with filters and mapping inheritance, allowing you to take the same object structure but populate it based on different criteria. The alias / filter / mapping inheritance capability is one of the most powerful aspects of Elementary and can replace the need for most custom query methods.
Let's revisit our original Product object, but now combine it with this new functionality. We'll define several additional classes with custom queries:
using System;
using Adapdev.Text;
using Elementary.Attributes;
namespace Elementary.Northwind.Samples.Attributes.AliasesAndFilters
{
[ElementaryClass]
public class Product
{
[Persist]
public int CategoryID = 0;
[Persist]
public int ProductID = 0;
[Persist]
public string ProductName = "";
[Persist]
public int SupplierID = 0;
[Persist]
public string QuantityPerUnit = String.Empty;
[Persist]
public double UnitPrice = 0.00;
[Persist]
public int Discontinued = 0;
public override string ToString()
{
return StringUtil.ToString(this);
}
}
// Here we add several classes that are simply filtered versions
// of the product class, considered as "Aliases" in Elementary
[ElementaryClass(Table="Products", SQLFilter = "Discontinued = 1")]
public class DiscontinuedProduct : Product
{ }
[ElementaryClass(Table = "Products", SQLFilter = "ProductName LIKE 'C%'")]
public class DiscontinuedProductStartingWithC : DiscontinuedProduct
{ }
// You can not only add sql filtering, but also extend what elements are mapped
[ElementaryClass(Table = "Products", SQLFilter = "UnitsInStock=0")]
public class OutOfStockProduct : Product
{
[Persist]
public int UnitsInStock = 0;
[Persist]
public int UnitsOnOrder = 0;
[Persist]
public int ReorderLevel = 0;
}
}
As far as the initial class mapping for Product, nothing has changed. However, we've now added three new mappings:
DiscontinuedProduct
OutOfStockProduct
DiscontinuedProductStartingWithC
You'll note several things. They all are using custom SQLFilters to modify what's returned. DiscontinuedProductStartingWithC extends DiscontinuedProduct, which demonstrates deep inheritance. Lastly, the OutOfStockProduct extends Product, but also maps several new fields.
Let's look at how to use these. First, we'll get all Products:
using System;
using System.Collections;
using Adapdev.Transactions;
using Elementary;
using Elementary.Northwind.Samples;
using NUnit.Framework;
using System.Collections.Generic;
namespace Elementary.Northwind.Samples.Attributes.AliasesAndFilters
{
/// <summary>
/// Summary description for Test.
/// </summary>
[TestFixture]
public class Test
{
[TestFixtureSetUp]
public void SetUp()
{
Elementary.Clear();
Elementary.LoadAssembly("Elementary.Northwind.Samples.dll");
}
[Test]
public void GetProductCollection()
{
IObjectMapper<Product> mapper = Elementary.GetIObjectMapper<Product>();
IList<Product> al = mapper.GetCollection();
Assert.IsTrue(al.Count > 0, "No products loaded.");
Assert.AreEqual(77, al.Count, "Not all products were loaded.");
Console.WriteLine("Loaded {0} products.", al.Count);
Console.WriteLine("\nFirst product:\n{0}", al[0]);
}
}
}
Output:
Loaded 77 products. First product: Properties for: Product CategoryID(System.Int32): 1 ProductID(System.Int32): 1 ProductName(System.String): Chai SupplierID(System.Int32): 1 QuantityPerUnit(System.String): 10 boxes x 20 bags UnitPrice(System.Double): 0 UnitsInStock(System.Int32): 0 UnitsOnOrder(System.Int32): 0 ReorderLevel(System.Int32): 0 Discontinued(System.Int32): 0
Now let's do the same for DiscontinuedProduct:
[Test]
public void GetDiscontinuedProductCollection()
{
IObjectMapper<DiscontinuedProduct> mapper = Elementary.GetIObjectMapper<DiscontinuedProduct>();
IList<DiscontinuedProduct> al = mapper.GetCollection();
// Display the sql call
Console.WriteLine(mapper.RecentStatements[0]);
Assert.IsTrue(al.Count > 0, "No products loaded.");
Assert.AreEqual(8, al.Count, "Not all discontinued products were loaded.");
Console.WriteLine("Loaded {0} products.", al.Count);
Console.WriteLine("\nFirst product:\n{0}", al[0]);
}The DiscontinuedProduct alias will map all of the fields defined in Product, but only retrieve Products that are discontinued. Here is the output, which includes the sql that was generated:
SELECT [unitsinstock], [reorderlevel], [unitsonorder],
[unitprice], [supplierid], [categoryid], [productid],
[discontinued], [productname], [quantityperunit]
FROM [products]
WHERE Discontinued = 1
Loaded 8 products.
First product:
Properties for: Product
CategoryID(System.Int32): 2
ProductID(System.Int32): 5
ProductName(System.String): Chef Anton's Gumbo Mix
SupplierID(System.Int32): 2
QuantityPerUnit(System.String): 36 boxes
UnitPrice(System.Double): 0
UnitsInStock(System.Int32): 0
UnitsOnOrder(System.Int32): 0
ReorderLevel(System.Int32): 0
Discontinued(System.Int32): 0You'll note that the Product mapping retrieved 77 objects, but the DiscontinuedProduct mapping retrieved only 8.
If we look at DiscontinuedProductStartingWithC, the list will be narrowed even further:
[Test]
public void GetDiscontinuedProductStartingWithCProductCollection()
{
IObjectMapper<DiscontinuedProductStartingWithC> mapper = Elementary.GetIObjectMapper<DiscontinuedProductStartingWithC>();
IList<DiscontinuedProductStartingWithC> al = mapper.GetCollection();
// Display the sql call
Console.WriteLine(mapper.RecentStatements[0]);
Assert.IsTrue(al.Count > 0, "No products loaded.");
Assert.AreEqual(1, al.Count, "Not all discontinued products were loaded.");
Console.WriteLine("Loaded {0} products.", al.Count);
Console.WriteLine("\nFirst product:\n{0}", al[0]);
}Note that DiscontinuedProductStartingWithC extends DiscontinuedProduct, which extends Product. If we look at the generated sql, you'll note that the sqlFilters from DiscontinuedProduct and DiscontinuedProductStartingWithC have been combined:
SELECT [unitsinstock], [reorderlevel], [unitsonorder],
[unitprice], [supplierid], [categoryid], [productid],
[discontinued], [productname], [quantityperunit]
FROM [products]
WHERE ProductName LIKE 'C%' AND Discontinued = 1
Loaded 1 products.
First product:
Properties for: Product
CategoryID(System.Int32): 2
ProductID(System.Int32): 5
ProductName(System.String): Chef Anton's Gumbo Mix
SupplierID(System.Int32): 2
QuantityPerUnit(System.String): 36 boxes
UnitPrice(System.Double): 0
UnitsInStock(System.Int32): 0
UnitsOnOrder(System.Int32): 0
ReorderLevel(System.Int32): 0
Discontinued(System.Int32): 0In the above examples, the properties UnitsInStock, UnitsOnOrder and ReorderLevel were never populated, since they weren't included in the Product mapping. However, the OutOfStockProduct mapping adds those fields. Because of this, we can now query using the OutOfStockProduct alias and those properties should be populated:
[Test]
public void GetOutOfStockProductCollection()
{
IObjectMapper<OutOfStockProduct> mapper = Elementary.GetIObjectMapper<OutOfStockProduct>();
IList<OutOfStockProduct> al = mapper.GetCollection();
// Display the sql call
Console.WriteLine(mapper.RecentStatements[0]);
Assert.IsTrue(al.Count > 0, "No products loaded.");
Assert.AreEqual(5, al.Count, "Not all discontinued products were loaded.");
Console.WriteLine("Loaded {0} products.", al.Count);
Console.WriteLine("\nFourth product:\n{0}", al[3]);
}Output:
SELECT [unitsinstock], [reorderlevel], [unitsonorder],
[unitprice], [supplierid], [categoryid], [productid],
[discontinued], [productname], [quantityperunit]
FROM [products]
WHERE UnitsInStock=0
Loaded 5 products.
First product:
Properties for: Product
CategoryID(System.Int32): 4
ProductID(System.Int32): 31
ProductName(System.String): Gorgonzola Telino
SupplierID(System.Int32): 14
QuantityPerUnit(System.String): 12 - 100 g pkgs
UnitPrice(System.Double): 0
UnitsInStock(System.Int32): 0
UnitsOnOrder(System.Int32): 70
ReorderLevel(System.Int32): 20
Discontinued(System.Int32): 0Last, you can further filter objects within your code:
[Test]
public void GetOutOfStockProductCollectionUsingSqlFilter()
{
IDataMapper<OutOfStockProduct> mapper = Elementary.GetIDataMapper<OutOfStockProduct>();
int original = mapper.GetCollection().Count;
IList<OutOfStockProduct> filtered = mapper.GetCollectionUsingSql("ReorderLevel > 5");
int revised = filtered.Count;
Console.WriteLine(String.Format("Original:{0}\nFiltered:{1}", original, revised));
Console.WriteLine("Original Call: " + mapper.RecentStatements[0]);
Console.WriteLine("Filtered Call: " + mapper.RecentStatements[1]);
}In the above example, we've switched from IObjectMapper to IDataMapper. IDataMapper extends IObjectMapper and adds several database specific options, such as retrieving a DataSet, doing custom sql calls, etc. You'll note that we make two calls in this example. One is GetCollection, which just uses the defined alias mapping. Then we call GetCollectionUsingSql and add an additional filter, saying that we only want OutOfStockProducts with a ReorderLevel > 5. In the output, you can see the number of objects returned and the different sql statements that were generated:
Original:5
Filtered:1
Original Call: SELECT [unitsinstock], [reorderlevel], [unitsonorder],
[unitprice], [supplierid], [categoryid], [productid],
[discontinued], [productname], [quantityperunit]
FROM [products]
WHERE UnitsInStock=0
Filtered Call: SELECT [unitsinstock], [reorderlevel], [unitsonorder],
[unitprice], [supplierid], [categoryid], [productid],
[discontinued], [productname], [quantityperunit]
FROM [products]
WHERE UnitsInStock=0 AND ReorderLevel > 5