Category Archives: Programming

Track Google Analytics on Angular2

Nowadays Single Page (Web) Application (SPA) is taking over traditional full post-back website. Google Analytics standard tracking code is though yet to support SPA website. We then have to manually implement to track route change.

Google Analytics , at the moment, provides this kind of tracking js code.

(function(i,s,o,g,r,a,m){i[‘GoogleAnalyticsObject’]=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,’script’,’https://www.google-analytics.com/analytics.js’,’ga’);

ga(‘create’, ‘UA-123456-1’, ‘auto’);
ga(‘send’, ‘pageview’);

Per my previous post – Passing javascript global variable to Angular2 (Typescript). We can archive by sending ga object to Angular2. You can choose either approach 1 or 2.

Then at the root component, AppComponent in my previous post, update constructor as follow.

constructor(private router: Router, @Inject(‘ga’) ga:any) {
router.subscribe((val) => {
ga(‘send’, ‘pageview’, { page: val });
});
}

We need to inject router to the constructor and inject ga variable from javascript also.

router has ability to subscribe route change event. We make use of it and call ga function to post tracking to Google Analytics. val parameter is path of target route.

Updated version of Angular2 probably also updated the router class. Please try following if you use a newer version of Angular2.

router.changes.subscribe((val) => {
ga(‘send’, ‘pageview’, { page: val });
});

Then you can use Live overview of Google Analytics to validate if it works properly. Enjoy ūüôā

Tagged , , ,

Passing javascript global variable to Angular2 (Typescript)

This content refers to angular2 version 2.0.0-beta.8. If it does not work for you probably because of breaking change in the future updates.

There are a couple ways to do this but I would basically mention only 2 approach.

The first approach, simply add declare var in component that you need to use.

In javascript code, declare variable in global scope:

var func = function() { … }

In Angular2, in the component that need to refer to javascript variable simply declare the same variable with the same name.

declare var func:Function;

This is the easiest way to archive the need but not the most efficient way. It looks like to be a magic variable in the component and is a great enemy of unit testing.

Since dependency injection (DI) pattern is one of the core concept of the¬† Angular2 (and AngularJS) and we should comply that ūüôā

The second approach, import and inject from javascript to Angular2.

In javascript code, declare variable in global scope:

var func = function() { … }

at import statement which normally in the main html page. Passing func variable to the main function of main module.

System.import(‘app/main’)
.then((module) => module.main(func));

Update main.ts TypeScript file. Wrap Angular2 bootstrap with a main function which accept a parameter from import statement above. Inject func variable using provide feature.

import {provide} from ‘angular2/core’;

…….

export function main(func) {
bootstrap(AppComponent, [
//…providers and services
provide(‘func’, { useValue: func})
]);
}

In AppComponent that bootstrap from main.ts, inject func variable to constructor and use as needed. For more information about @Inject decorator please refer to this screencast tutorial – https://egghead.io/lessons/angular-2-using-the-inject-decorator.

export class AppComponent {
constructor(@Inject(‘func’) func:Function) {
// use func variable as needed
}
}

Now you have access to javascript variable and retain dependency injection concept and of course no magic variable!

 

 

Tagged , ,

D3 Network (Force Directed) Render with Node Margin

I had to build the network (massive relation) diagram to (potentially) replace the regular javascript drawing module due to the data to render getting bigger everyday. Instead of enhance the existing library to support more data, I rather decided to find the replacement of drawing library for the long term solution. D3 seems to be the best candidate with a bunch of incredible examples.

This example http://bl.ocks.org/mbostock/4062045 was the good start to customize to cover all requirements that the old render library does. Although it had many features and customizations to add and one of them was adding margin between link line and node.

There are a couple of reasons you would need margin to add margin between endpoint of line and node.

    1. Display node with various shape using d3.svg.symbol(), margin would make the the node shape (diamond, rectangle, circle, etc.) not to be overlapped by the line.
    2. If you have many line link to a single node, especially if you add arrow to the end of line, margin would make its display much cleaner.

Give some free space for node’s label.

Below is a sample of render; without margin we wouldn’t be able to indicate it’s shape.

Image

In tick event of d3.layout.force(), I replace (if any) “link.attr(“d”, function(d) { });” with following:


link.attr("d", function(d) {
 // calculate new link endpoint for both source and target
 // with specified margin
 var sourceX = d.source.x,
     sourceY = d.source.y,
     targetX = d.target.x,
     targetY = d.target.y;

 var X = Math.abs(targetX - sourceX);
 var Y = Math.abs(targetY - sourceY);
 var R = Math.sqrt(X*X + Y*Y);
 var xMargin = X * nodeMargin / R;
 var yMargin = Y * nodeMargin / R;

 if (sourceX > targetX) {
 sourceX = sourceX - xMargin,
 targetX = targetX + xMargin;
 }
 else {
 sourceX = sourceX + xMargin,
 targetX = targetX - xMargin;
 }

 if (sourceY > targetY) {
 sourceY = sourceY - yMargin,
 targetY = targetY + yMargin;
 }
 else {
 sourceY = sourceY + yMargin,
 targetY = targetY - yMargin;
 }
 d.x1 = sourceX;
 d.y1 = sourceY;
 d.x2 = targetX;
 d.y2 = targetY;
})
.attr("x1", function(d) { return d.x1; })
.attr("y1", function(d) { return d.y1; })
.attr("x2", function(d) { return d.x2; })
.attr("y2", function(d) { return d.y2; });

See a sample here Рhttp://jsfiddle.net/iamarther/gZG5x/2/ (extended from http://bl.ocks.org/mbostock/4062045)

d3_mess_sample

Tagged , ,

OptionSet Batch Management for Microsoft CRM 2011

So long no post since the previous post. I used to work with Microsoft CRM 4.0 and Microsoft CRM 2011 for a few years back and made several tools for particular usages. I was also trying to blog and share them but something blocked me from finishing it. Here it is one of them I can recall the location.

I recently have a chance to back to work on Microsoft CRM 2011 after quite a long switched to custom application and web application development. I discovered that Microsoft CRM 2011 has now quite a lot of tools to ease the developer and/or administrator to develop and customize the system. There are many more than the last time I developed 2011 version. However, as we all know the clients never ending asking for a special feature which standard features of Microsoft CRM doesn’t provide. It is the reason why those complexity and huge systems like CRM and ERP need to have developers.

My latest client has many teams joining in the project and many people do entity customization. It causes inconsistency to be happened. One of the ruffle thing is that the value of OptionSets are like a jumping number. For instance; 1:Cash, 2:Credit Card, 1000000:Debit. This could easily happen if you have many distributed solutions(for dev maybe) to merge to UAT and PROD. Each solution could set it’s own configurable initial number of OptionSet value.

When I see this kind of mess, I really need to clean it up before hand it to production environment. The problem was the system was huge and custom OptionSets are all over the place. I saw several OptionSet manager tools but all of them aim one by one OptionSet edit and actually none of them touch the OptionSet’s value.¬† I can understand that it is unsafe to change the OptionSet value if it is referenced by any existing record. Though in some case like just before delivering the system to the new environment with completely blank data; we are safe to do this kind of clean up.

I so develop my own tool to manage this kind of need and I named it OptionSet Batch Manager for Microsoft CRM 2011. I uploaded to CodePlex here. For the first version, it could do reorder by value and name and resetting value of OptionSets either as a batch(upon selection) or individual. Hope someone find it useful!

OptionSet Batch Manager screenshot

OptionSet Batch Manager screenshot

Addition note: I used Reactive Extension or so called Rx to manage thread for service calls. For anyone who interested, you can either use TFS to checkout the full source code from CodePlex or directly download source code which I will soon upload.

I’ll share some tricks to compare and merge two inconsistent CRM 2011 solutions in the next post.

Tagged , , , , , ,

Update Service Reference then get error – The maximum nametable character count quota (16384) has been exceeded while reading XML data.

If you have WCF and trying to update service reference via Visual Studio then right away get error message saying that “The maximum nametable character count quota (16384) has been exceeded while reading XML data”, this means your service becomes too big.

This was just because of the service grows too big and exceed the maximum size that default setting examined. So we have to expand the limitation of size of Visual Studio so we can update service through Visual Studio as usual again.

1. Go to C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE (up to the version you are using, mine is Visual Studio 2010).

2. Find devenv.exe.config and open to edit it with Administrator right, thus the editor must be opened as Administrator. Otherwise you won’t be able to save change.

3. Add below config for service model then save it.


<system.serviceModel>

 <client>

 <endpoint binding="netTcpBinding" bindingConfiguration="GenericBinding" contract="IMetadataExchange" name="net.tcp" />

 </client>

 <bindings>

 <netTcpBinding>

 <binding name="GenericBinding"

 maxBufferPoolSize="2147483647" maxBufferSize="2147483647"

 maxReceivedMessageSize="2147483647">

 <readerQuotas maxDepth="2147483647"

 maxStringContentLength="2147483647"

 maxArrayLength="2147483647"

 maxBytesPerRead="2147483647"

 maxNameTableCharCount="2147483647" />

 <security mode="None" />

 </binding>

 </netTcpBinding>

 </bindings>

 </system.serviceModel>

Note that line 3, 6 and 17 are up to the protocol that you are using.

4. Restart Visual Studio if opening.

Done. You should be able to update service via Visual Studio now.

Tagged , , , , ,

Use Transactions and Rollback for Unit Testing against WCF RIA Services

Apparently Silverlight provide use of WCF RIA Services as a data access tool, it behaves just like an ORM tool but over the wire.

How it works ? Well, it names express quite straightforward. It has WCF as interface to let client request and response the data. At the client side, it does generate sort of proxy as same as regular WCF or even old-fashioned web service does. It uses Entity Framework as an ORM tool to manipulate database.

In this post, I will assume that we are going to write unit test against server operations. So proxy and communication over the wire thing will not be considered.

The approach that I’m going to show you is pretty much the same as my previous post “Use Transactions and Rollback for Unit Testing against Entity Framework“, because as I mentioned that RIA uses Entity Framework as ORM tool.

Note that you will need to keep classes from 1-3 in RIA Services project, 4-5 in Test Project.

1. Create a new class that override DBTransaction class.

  • Make the constructor to accept a parameter of DbTransaction object. So this class acts just like an adapter of DbTransaction class.
  • By pass the real rollback and commit behavior by overriding Rollback and Commit functions so actual rollback and commit functions will not do anything.
  • Create RollbackUnitTest function to be called from another class that we are going to write it up next.
public class TestDbTransaction : System.Data.Common.DbTransaction
{
   public DbTransaction ActualDBTransaction { get; private set; }

   public TestDbTransaction(DbTransaction actualDBTransaction)
   {
      ActualDBTransaction = actualDBTransaction;
   }

   public override void Rollback()
   {
      // do nothing : the test transaction is already running
   }

   public override void Commit()
   {
      // do nothing : the test transaction is already running
   }

   public void RollbackUnitTest()
   {
      ActualDBTransaction.Rollback();
   }

   protected override System.Data.Common.DbConnection DbConnection
   {
      get { return ActualDBTransaction.Connection; }
   }

   public override IsolationLevel IsolationLevel
   {
      get { return ActualDBTransaction.IsolationLevel; }
   }
}

2.  Create extension methods of ObjectContext named BeginTransaction to begin the transaction and return TestDbTransaction object out.

  public static class TestObjectContextExtender
  {
        public static System.Data.Common.DbTransaction BeginTransaction(this System.Data.Objects.ObjectContext context, IsolationLevel isolationLevel)
        {
            var actualTransaction = context.Connection.BeginTransaction(isolationLevel);
            return new TestDbTransaction(actualTransaction);
        }

        public static System.Data.Common.DbTransaction BeginTransaction(this System.Data.Objects.ObjectContext context)
        {
            return new TestDbTransaction(context.Connection.BeginTransaction());
        }
  }

3. Create a partial class of Domain Service.
We need to get access to the ObjectContext which has access level as protected. So we can either override it or create partial class. I go with partial and make extension method to avoid lots of changes.

Don’t forget to replace¬†YOURDomainService and YOUREntities with yours.


    public partial class YOURDomainService : LinqToEntitiesDomainService<YOUREntities>
    {
        public TestDbTransaction BeginTransactionForUnitTest()
        {
            this.ObjectContext.Connection.Open();
            return this.ObjectContext.BeginTransaction() as TestDbTransaction;
        }

        public YOUREntities GetDataContext()
        {
            return this.ObjectContext;
        }
    }

4. Create a base class to be inherited by each unit test class.

TestStart method is being called before every unit test and TestEnd after. This is the result of pasting attribute TestInitialize and TestCleanup on them.

    [TestClass()]
    public class TransactedTestClass
    {
        protected GeneralDomainService _service;
        private TestDbTransaction _transaction;

        [TestInitialize()]
        public void TestStart()
        {
            DomainServiceContext context = new DomainServiceContext(new ServiceProvider(), DomainOperationType.Query);
            _service= new GeneralDomainService();
            _service.Initialize(context);

            _transaction = _service.BeginTransactionForUnitTest();
        }

        [TestCleanup()]
        public void TestEnd()
        {
            _transaction.RollbackUnitTest();
        }
    }

5. Sample of use. Create a test class and inherit from TransactedTestClass.

  • _service is an instance declared in TransactedTestClass.
  • GetDataContext method is declared in partial class in 3.
[TestClass()]
public class GeneralDomainServiceTest : TransactedTestClass
{
        [TestMethod()]
        public void AddNewUser_Test()
        {
            // Get ObjectContext for validation (directly query to database).
            var dataContext = _service.GetDataContext();
            // Assume that our test database has no record in Users table
            Assert.AreEqual(0, dataContext.Users.Count());
            _service.AddNewUser(new User());
            // Verify that one record is added
            Assert.AreEqual(1, dataContext.Users.Count());
        }
}
Tagged , , , , , , , ,

Use Transactions and Rollback for Unit Testing against Entity Framework

Refer to my previous post that talked about Unit Test with Rollback Pattern, Entity Framework is one of ORM tool that developer has to learn it to use accurately. So if you use Mock to do such a by pass of querying against Entity Framework, it is pretty unsafe really. Except you are very confident that you (or your developer) are mastery of Entity Framework.

First of all you need to have a separate database for test only and prepare some data for testing purpose. Then change the connection string in config file to point to this database rather than the development database.

Here is how I wrap and override the object context.

1. Create a new class that override DBTransaction class.

  • Make the constructor to accept a parameter of DbTransaction object. So this class acts just like an adapter of DbTransaction class.
  • By pass the real rollback and commit behavior by overriding Rollback and Commit functions so actual rollback and commit functions will not do anything.
  • Create RollbackUnitTest function to be called from another class that we are going to write it up next.
public class TestDbTransaction : System.Data.Common.DbTransaction
{
   public DbTransaction ActualDBTransaction { get; private set; }

   public TestDbTransaction(DbTransaction actualDBTransaction)
   {
      ActualDBTransaction = actualDBTransaction;
   }

   public override void Rollback()
   {
      // do nothing : the test transaction is already running
   }

   public override void Commit()
   {
      // do nothing : the test transaction is already running
   }

   public void RollbackUnitTest()
   {
      ActualDBTransaction.Rollback();
   }

   protected override System.Data.Common.DbConnection DbConnection
   {
      get { return ActualDBTransaction.Connection; }
   }

   public override IsolationLevel IsolationLevel
   {
      get { return ActualDBTransaction.IsolationLevel; }
   }
}


2.  Create extension methods of ObjectContext named BeginTransaction to begin the transaction and return TestDbTransaction object out.

  public static class TestObjectContextExtender
  {
        public static System.Data.Common.DbTransaction BeginTransaction(this System.Data.Objects.ObjectContext context, IsolationLevel isolationLevel)
        {
            var actualTransaction = context.Connection.BeginTransaction(isolationLevel);
            return new TestDbTransaction(actualTransaction);
        }

        public static System.Data.Common.DbTransaction BeginTransaction(this System.Data.Objects.ObjectContext context)
        {
            return new TestDbTransaction(context.Connection.BeginTransaction());
        }
  }

3. Create a base class to be inherited by each unit test class.

TestStart method is being called before every unit test and TestEnd after. This is the result of pasting attribute TestInitialize and TestCleanup on them.

    [TestClass()]
    public class TransactedTestClass
    {
        protected DBEntities _dataRollbackContext;
        private TestDbTransaction _transaction;

        [TestInitialize()]
        public void TestStart()
        {
            _dataRollbackContext = new DBEntities();
            _dataRollbackContext.Connection.Open();
            _transaction = _dataRollbackContext.BeginTransaction() as TestDbTransaction;
        }

        [TestCleanup()]
        public void TestEnd()
        {
            _transaction.RollbackUnitTest();
        }
    }

4. Sample of use. Create a test class and inherit from TransactedTestClass.

[TestClass()]
public class DataRepositoryTest : TransactedTestClass
{
        [TestMethod()]
        public void AddNewUser_Test()
        {
            // In this example I made up the data repository and use dependency injection for the data context.
            DataRepository target = new DataRepository(_dataRollbackContext);
            // Assume that our test database has no record in Users table
            Assert.AreEqual(0, _dataRollbackContext.Users.Count());
            target.AddNewUser(new User());
            // Verify that one record is added
            Assert.AreEqual(1, _dataRollbackContext.Users.Count());
        }
}

There are other approaches that expect the same result.

  • One is wrapping up each unit test with usage of TransactionScope without commit statement. This ensure that any change to the data in database will be rollback when the unit test is done. ¬†Even though, this seems to be easy and straightforward but because of Entity Framework basically begins transaction automatically and commits once SaveChanges method is being called. Begin transaction within another transaction requires MSDTC. Thus,¬†MSDTC must be enabled and this is not make any sense.
  • Second is doing a database snapshot and restore every time the test completed. This approach is fairly complicated and difficult to manage.
Tagged , , , , , , , , ,

Prevent Browser Back Button for Silverlight with Confirm Dialog

One of common issues of the Silverlight, is that sometime the user accidentally clicks the browser back button. Since the  Silverlight Application hosts on a HTML page as a Frame. Any action on Silverlight does not cause the browser to keep state. So if the user clicks back button of browser, it would just go back to the page just before it reach the page hosting the Silverlight Application.

Silverlight has Navigation Framework that take care of navigate history over the pages. But some developers may decide not to use it. Or the application goes too far to add such this core functionality.

As many forums have discussed about disable browser back button. There is no direct solution and the browser should not provide it as well. It’s not fair to limit the user not to go out of the website if it really needs to. We can alternatively open the webpage that host the Silverlight Application in new window.¬† This way can guarantee that no previous page thus the browser back button disables itself. But this workaround is not elegant and unacceptable by my customer.

I then made another tricky workaround to confirm to the user when they clicked the browser back button.


Tested with IE8, Firefox and Chrome.

A couple of things needed to add in order to do this.

1. Insert a new page with a Frame Control at the root of application. This page does capture and confirm when the user clicked the back button.

XAML

    <Grid x:Name="LayoutRoot">
        <sdk:Frame Name="frame1" Source="/MainPage" HorizontalAlignment="Stretch" VerticalAlignment="Stretch">
            <sdk:Frame.UriMapper>
                <sdk:UriMapper>
                    <sdk:UriMapping Uri="/MainPage" MappedUri="/ActualMainPage.xaml" />
                </sdk:UriMapper>
            </sdk:Frame.UriMapper>
        </sdk:Frame>
    </Grid>

Code

public partial class MainPage : Page
    {
        private bool _doNotBlockNavigation = false;

        public MainPage()
        {
            InitializeComponent();

            this.frame1.Navigating += new NavigatingCancelEventHandler(frame1_Navigating);
            this.frame1.NavigationStopped += new NavigationStoppedEventHandler(frame1_NavigationStopped);
        }

        void frame1_NavigationStopped(object sender, NavigationEventArgs e)
        {
            ConfirmWindow confirm = new ConfirmWindow();
            confirm.Closed += (s, ea) =>
            {
                if (((ConfirmWindow)s).DialogResult ?? false)
                {
                    // Invoke Javascript function that simply go back the history
                    HtmlPage.Window.Invoke("GoBack");
                }
                else
                {
                    // Get current page from the Frame
                    var mainPage = frame1.Content as Page;
                    // Get the NavigationService of current page
                    var navigationService = mainPage.NavigationService;

                    // If the user refuses to leave the page then
                    // mark flag not to block GoBack() process
                    _doNotBlockNavigation = true;
                    if (navigationService.CanGoBack)
                        navigationService.GoBack();
                }
            };
            confirm.DataContext = "Are you really want to leave this application to go back ?";
            confirm.Show();
        }

        void frame1_Navigating(object sender, NavigatingCancelEventArgs e)
        {
            // if _doNotBlockNavigation = true then just set it back to false so the navigation works properly for the next time.
            if (_doNotBlockNavigation)
            {
                _doNotBlockNavigation = false;
            }
            else if (e.NavigationMode == NavigationMode.New && e.Uri.OriginalString == "")
            {
                // If the user presses browser's back button then temporary blocks from navigating out.
                // This would course the event NavigationStopped to be fired.
                e.Cancel = true;
            }
        }
    }

Note that the ConfirmWindow on line 15 is my custom Child Window. It behaves just like a regular confirm dialog.
Also if you did not create the project as Navigation Silverlight Project, you would need to add reference to Navigation library.


2. Add this function to the javascript block of aspx/html host page. This function will be called by line 21 of above code-snippet.

function GoBack() {
     history.go(-1);
}


3. Make sure that ActualMainPage.xaml overrides from Page not UserControl. Also do not forget to put the appropriate title for ActualMainPage.xaml, it will be shown as the HTML title. If the Title attribute is not set, it will take the current URI from UriMapper.

4. Modify App.xaml.cs to set root as MainPage instead of ActualMainPage.

this.RootVisual = new MainPage();



5. That is it! It should work properly now.

If you think this post is useful, please leave your comments.

Tagged , ,

Unit test with rollback pattern

For the person who is not familiar with Unit Testing yet. We basically have two options to do unit testing against functions that do CRUD operations, probably with some easy and/or complex logic. They are Mock and Rollback patterns. To keep this post short, I wouldn’t go into details of what the Mock is but you can search it yourself, there is a bunch of contents out there. This post will go with the Rollback pattern.¬† So, what is the Rollback pattern?

Well, we need to rollback what we did change to the data in database by the function that the unit test invokes. Including operation(s) in unit test that we may need to execute and it does change data in database.

Pretty simple example. If we run this below test twice, Assert will fail in the second run because the user Id 1 was already deleted by the first run.

 [TestMethod]
 public void DeleteUser_Test()
 {
    int userId = 1;

    bool isDeleted = service.DeleteUser(userId);

    Assert.IsTrue(isDeleted);
 }

The easiest way is to use TransactionScope to wrap it up.

 [TestMethod]
 public void DeleteUser_Test()
 {
    int userId = 1;
    using(TransactionScope ts = new TransactionScope())
    {
       bool isDeleted = service.DeleteUser(userId);

       Assert.IsTrue(isDeleted);
    }
 }

Without calling ts.Complete(), the transaction ts would rollback what DeleteUser function did.

If you are using ORM, you must ensure that it does support TransactionScope. I’m sure that LLBLGen, LinqToSQL, LinqToEntities (Entity Framework) do support.

Also beware of executing the store procedure that creates another transaction inside. If you wrap this calling up in TransactionScope, the TransactionScope does not take over the transaction created inside the store procedure. This means that the TransactionScope couldn’t rollback the transaction inside the store procedure.

I would recommend not to use Rollback Unit test if the testing service executes the store procedure.

Tagged , , , ,

Filter Sub Entity in Query of LinqToEntities DomainService

If you are one of the developers, who are working on the project that use Domain Service with LinqToEntities, this common issue should annoy you sometimes.

Normally we use Include(“RelatedEntity”) to tell LinqToEntitiesDomainService to also retrieve ALL¬†records of RelatedEntity that have relation to the main querying entity.

For example with this data:
Customer Table

CustomerId Name Sex
1 Mr. Wise Male
2 Mrs. Fool Female

Order Table

OrderId CustomerId Total PaymentTypeId
1 1 $5000 1
2 1 $200 1

PaymentType Table

PaymentTypeId PaymentTypeName
1 Cash
2 Credit Card

With this sample data, if we need all Male Customers along with their Orders. It would look like this.

var customers = from c in this.ObjectContext.Customers.Include("Orders")
                where c.CustomerId == 1
                select c;

But what if we need all Male Customers along with their Orders that Total over $1000 ONLY ?

Well, there is no regular operation to embed condition into the query for filtering out the sub entities. The Where statement only filters the main entity, the Customer in this case.

There is a workaround by modify query with this tricky query.

var customers = (from c in this.ObjectContext.Customers
                 where c.CustomerId == 1
                 select new {
                   MaleCustomer = c,
                   OrderOverThousand = c.Orders.Where(o => o.Total > 1000)
                 }).ToList().Select(c=>c.MaleCustomer);

Note that you do not need to have Include operation for this query. It does populate all entities you specified in the enclosed anonymous class after the Select statement.

You may find this workaround in several blogs but I couldn’t find two level depth of related entity.

With the same query condition but also expect PaymentType of Orders, it would look like this:

var customers = (from c in this.ObjectContext.Customers
                 where c.CustomerId == 1
                 let filteredOrders = c.Orders.Where(o => o.Total > 1000)
                 select new {
                   MaleCustomer = c,
                   OrderOverThousand = filteredOrders,
                   OrderPaymentType = filteredOrders.Select(o => o.PaymentType)
                 }).ToList().Select(c=>c.MaleCustomer);

As you can see, all you have to do is just put another property of anonymous class as many entities as you want. You may doubt, “What does the let statement do?” and “Why do we need it?”. It is optional but it can save you from unnecessary addition Select operation when the context transforms it into the SQL statement.

As you can see at line number 6 and 7, without let statement, it would be c.Orders.Where(o => o.Total > 1000) instead of the filteredOrders variable.  As a result, both will do Select operation twice for the same result set.

Hope this post can elucidate some who struggle in such problems¬†ūüėČ .

Tagged , , , , , ,