Lets start here. I have the following two classes:
public class Order
{
public Int32 Id { get; set; }
public String OrderNumber { get; set; }
public DateTime OrderDate { get; set; }
public String OrderStatus { get; set; }
public Int32 CustomerId { get; set; }
public List<OrderItem> Items { get; set; }
public Decimal Total { get; set; }
}
public class OrderItem
{
public Int32 OrderId { get; set; }
public Int32 OrderLineNumber { get; set; }
public Int32 ItemId { get; set; }
public Double OrderQty { get; set; }
public Decimal Price { get; set; }
public Decimal LineTotal { get; set; }
}
Here is some LINQ query that am using to fetch Orders from two database tables (OrderMaster and OrderDetail) and return a List<Order>. The LINQ DataContext that has the two tables is named TempContext.
static List<Order> GetOrders()
{
using (var context = new TempContext())
{
return (from om in context.OrderMasters
select new Order
{
Id = om.OrderID,
OrderNumber = om.OrderNumber,
OrderDate = om.OrderDate,
OrderStatus = om.OrderStatus,
CustomerId = om.CustomerID,
Total = om.Total,
Items = (from od in context.OrderDetails.Where(d => d.OrderID == om.OrderID)
select new OrderItem
{
OrderLineNumber = od.OrderLineNumber,
OrderId = od.OrderID,
ItemId = od.ItemID,
OrderQty = od.OrderQty,
Price = od.Price,
LineTotal = od.Total
}).ToList()
}).ToList();
}
}
My idea is to convert this LINQ Query to a Compiled LINQ Query and see whether I will be able to get any notable improvements on execution performance. Here is what I was able to do:
static List<Order> GetCompiledOrders()
{
using (var context = new TempContext())
{
return (from om in CompiledOrders(context)
select new Order
{
Id = om.OrderID,
OrderNumber = om.OrderNumber,
OrderDate = om.OrderDate,
OrderStatus = om.OrderStatus,
CustomerId = om.CustomerID,
Total = om.Total,
Items = (from od in CompiledItems(context, om.OrderID)
select new OrderItem
{
OrderLineNumber = od.OrderLineNumber,
OrderId = od.OrderID,
OrderQty = od.OrderQty,
ItemId = od.ItemID,
Price = od.Price,
LineTotal = od.Total
}).ToList()
}).ToList();
}
}
public static readonly Func<TempContext, IQueryable<OrderMaster>>
CompiledOrders =
CompiledQuery.Compile<TempContext, IQueryable<OrderMaster>>
((context)
=>
from om in context.OrderMasters
select om
);
public static readonly Func<TempContext, Int32, IQueryable<OrderDetail>>
CompiledItems =
CompiledQuery.Compile<TempContext, Int32, IQueryable<OrderDetail>>
((context, orderId)
=>
from od in context.OrderDetails
select od
);
The above code worked. Notice that I did two compiled queries. One to get me the Orders and the other to get me the OrderItems which I then combined in GetCompiledOrders method. I would like to know how else I can do this query so that I can use one compiled query and still have OrderItems [Items] as List<OrderItem> (not IQueryable<OrderItem> ) and still have GetCompiledOrders return List<Order>.
And yes, I googled. Most examples on how to create compiled queries cover returning a simple list (with no child collection). And yes, its possible that the NESTED LINQ way is the PROBABLY the best bet when it comes to these scenarios. Your thoughts are welcome. Thanks for your time. (And sorry for the long post).
Below is a script you can use to create OrderMaster and OrderDetail table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderMaster](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderNumber] [nvarchar](10) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[OrderStatus] [nvarchar](36) NOT NULL,
[Total] [money] NOT NULL,
CONSTRAINT [PK__OrderMaster] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderMaster] ADD CONSTRAINT [DF_OrderMaster_Total] DEFAULT ((0.0)) FOR [Total]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderDetail](
[OrderID] [int] NOT NULL,
[OrderLineNumber] [int] NOT NULL,
[ItemID] [int] NOT NULL,
[OrderQty] [float] NOT NULL,
[Price] [money] NOT NULL,
[Total] [money] NOT NULL,
CONSTRAINT [PK__OrderDetail] PRIMARY KEY CLUSTERED
(
[OrderID] ASC,
[OrderLineNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderDetail] ADD CONSTRAINT [DF_OrderDetail_OrderQty] DEFAULT ((1.0)) FOR [OrderQty]
GO
ALTER TABLE [dbo].[OrderDetail] ADD CONSTRAINT [DF_OrderDetail_Price] DEFAULT ((0.0)) FOR [Price]
GO
ALTER TABLE [dbo].[OrderDetail] ADD CONSTRAINT [DF_OrderDetail_Total] DEFAULT ((0.0)) FOR [Total]
GO
|