This post goes into a bit more detail.
There are many examples out there in the communities in how to do simple queries, stuff like sorting filtering, grouping selecting new anonymous types etc. But what if we already have a relational schema that represents a one-to-one or a one-to-many mapping class hierarchy to our database schema which contains multiple records and we want to do some processing at a very low level in the hierarchy. Consider the schema defined below:
I've modelled this using SQL Server but I could have used quite a few different tools to do this.
The schema is quite nice to work with and it would be nice to have an object model that represented this in code.
In Part 1 we modeled the Customer table although notice in the above schema we have two new columns: HomePhone and MobilePhone so we will add these to our object model as follows:
public class CustomerInfo
{
private List<OrderInfo> orders = null;
As you can see we now have the two additional properties added to support the additional columns and also we have an Orders property which gives us access to all orders for each customer.
public CustomerInfo()
{
orders = new List<OrderInfo>();
}
public int CustomerId
{
get;
set;
}
public string Name
{
get;
set;
}
public string AddressLine1
{
get;
set;
}
public string AddressLine2
{
get;
set;
}
public string AddressLine3
{
get;
set;
}
public string City
{
get; set;
}
public string County
{
get;
set;
}
public string PostalCode
{
get;
set;
}
public string Country
{
get;
set;
}
public string HomePhone
{
get;
set;
}
public string MobilePhone
{
get;
set;
}
public ListOrders
{
get
{
return orders;
}
set
{
orders = value;
}
}
}
Of course now we need both the Orders class the the Products class. They look like the following:
public class OrderInfoNotice from the above we have the Product property which gives us the product this order relates to. Now we need the product class:
{
public int OrderId
{
get; set;
}
public int CustomerId
{
get; set;
}
public int ProductId
{
get; set;
}
public int Quantity
{
get; set;
}
public ProductInfo Product
{
get; set;
}
}
public class ProductInfoThat's it in terms of our object model. Nothing too complex. If we wanted, we could mark each class as Serializable so that if we wanted to serialize the data into binary we could (binary serializer is not supported on the CF) this is not required for XML serialization however. I do want to show an example of serializing this too as it adds an element of coolness to this example of how powerful we can make LINQ.
{
public int ProductId
{
get;
set;
}
public string Description
{
get;
set;
}
public decimal Cost
{
get;
set;
}
}
So now we need some data. Normally you'd write some SQL to populate your object model as I mentioned in previous posts sadly LINQ to SQL and LINQ to ADO.NET/Entitys are not supported. So this hard work still has to be hand cranked. Although there is an ORM toolset available for the Compact Framework. You can find this here. I'd be interested in hearing from anyone their experiences of this toolset as I haven't personally tried it, yet.
As I don't have a database, I'm just going to create an object model by hand with some fictitious data that I just made up. So this code looks as follows:
List<CustomerInfo>customersSource = new List<CustomerInfo>So we have 2 orders for customer Joe (id 1) and no orders for any other customer.
{
new CustomerInfo
{
CustomerId = 1,
Name = "Joe",
City = "London",
Orders = new List()
{
new OrderInfo()
{
OrderId = 1,
CustomerId = 1,
ProductId = 1,
Quantity = 5,
Product = new ProductInfo()
{
ProductId = 1,
Cost = 23.34M,
Description = "Garden tiles"
}
},
new OrderInfo()
{
OrderId = 2,
CustomerId = 1,
ProductId = 2,
Quantity = 10,
Product = new ProductInfo()
{
ProductId = 2,
Cost = 56.12M,
Description = "Grass seed"
}
},
new OrderInfo()
{
OrderId = 3,
CustomerId = 1,
ProductId = 2,
Quantity = 1,
Product = new ProductInfo()
{
ProductId = 3,
Cost = 599.95M,
Description = "Table and chairs"
}
}
}
},
new CustomerInfo
{
CustomerId = 2,
Name = "Pete",
City = "Paris"
},
new CustomerInfo
{
CustomerId = 3,
Name = "John",
City = "New York"
},
new CustomerInfo
{
CustomerId = 4,
Name = "Pete",
City = "London"
},
new CustomerInfo
{
CustomerId = 5,
Name = "Paul",
City = "Dublin"
},
new CustomerInfo
{
CustomerId = 6,
Name = "Steve",
City = "Exeter"
},
new CustomerInfo
{
CustomerId = 7,
Name = "Clare",
City = "Norwich"
}
};
Suppose now that we wanted a list of customers who have placed an order. I could code somthing like the following:
var customersWithOrders = from c in customersSource
where c.Orders.Count > 0
c.Name;
The output of above looks like:
foreach (string s in customersWithOrders)
{
Debug.WriteLine(string.Format("Customer {0} has an order", s));
}
Customer Joe has an orderOrders will always be non-null because we initialize a collection of orders in the CustomerInfo class. We could have coded the above using the new feature of C# 3.0 which is anonymous types. An example of using anonymous types is as follows:
var customersWithOrders = from c in customersSourceWe could extend the anonymous type to include more information such as which city the customer is from:
where c.Orders.Count > 0
select new
{
c.Name
};
foreach (var s in customersWithOrders)
{
Debug.WriteLine(string.Format("Customer {0} has an order", s.Name));
}
var customersWithOrders = from c in customersSource
where c.Orders.Count > 0
select new
{
c.Name,
c.City
};
foreach (var s in customersWithOrders)We could also just select the customer like so:
{
Debug.WriteLine(string.Format("Customer {0} from city {1} has an order",
s.Name, s.City));
}
var customersWithOrders = from c in customersSource
where c.Orders.Count > 0
select c;
foreach (var s in customersWithOrders)The type is in fact a CustomerInfo object. It is implicitly inferred at runtime. The output of the above looks like the following:
{
Debug.WriteLine(string.Format
("Customer {0} from city {1} country {2} has an order",
s.Name, s.City, s.Country));
}
Customer Joe from city London country has an orderNotice how the country is blank, why is this? Go back to where we create our object model - we never intialized the Country property. In fact, as we are using auto-implemented properties the property will in fact be null. Cleverly though the code doesn't blow up because the above is in fact a bug.
You could from the var above do this:
CustomerInfo cust = customersWithOrders.First();Which would give us our first CustomerInfo object found in the collection. The var is in this case an IEnumerable and First is an extension method on IEnumerable. There are many extension methods on IEnumerable which I will show examples of how to use lambda expressions in queries.
So far we have seen very simple queries. Now what if we wanted to do something more complex like find out which customers have placed orders totaling more than £100.00.
We use a new feature of C# 3.0 called lambda expressions to do this which makes our code very short and easy to read. Lambda expressions are much like anonymous methods as introduced with C# 2.0. There are plenty of resources out in the community that talk about lambda expressions in depth. In fact whenever you use LINQ - for example in the simple examples above the compiler will be creating lambda expression calculations in the background, seemlessly. Of course you can explicitly use them yourself to add extra power.
The code to achieve the above could look like the following:
var customersWithOrdersOver100 = from c in customersSource
where c.Orders.Sum(o => o.Product.Cost) > 100
select c.Name;
foreach (var s in customersWithOrdersOver100)The output of the above looks like:
{
Debug.WriteLine(string.Format("Customer {0} has an order over £100.00", s));
}
Customer Joe has an order over £100.00Here we have used extension method Sum on the IEnumerable interface then using lambda expression we are able to execute the query.
How cool is that, in just 3 lines of code - that is the impressive thing here.
That's it for this post, but in my next post Part 3 I talk about some more things you can do with LINQ.
No comments:
Post a Comment