RSS

Tag Archives: linq

What is the Best and Fastest Way to Flatten Data?

Given the following data set, what’s the best and fastest way to flatten them? This can be achieved with LINQ, SQL, C#, or whatever else you prefer.

001 12/5/2013 113 143 11143 44 673
001 12/6/2013 113 143 11143 44 673
001 12/7/2013 113 143 11143 44 673
001 12/8/2013 113 143 11143 44 673
001 12/9/2013 113 143 11143 44 673
001 12/10/2013 113 143 11143 44 673
002 12/5/2013 113 143 11143 44 673
002 12/6/2013 113 143 11143 44 673
002 12/7/2013 113 143 11143 44 673
002 12/8/2013 113 143 11143 44 673
002 12/9/2013 113 143 11143 44 673
002 12/10/2013 113 143 11143 44 673

Desired result:

001 12/5/2013 113 143 11143 44 673 12/6/2013 113 143 11143 44 673 12/7/2013 113 143 11143 44 673 12/8/2013 113 143 11143 44 673 12/9/2013 113 143 11143 44 673 12/10/2013 113 143 11143 44 673
002 12/5/2013 113 143 11143 44 673 12/6/2013 113 143 11143 44 673 12/7/2013 113 143 11143 44 673 12/8/2013 113 143 11143 44 673 12/9/2013 113 143 11143 44 673 12/10/2013 113 143 11143 44 673
Advertisements
 
Leave a comment

Posted by on December 3, 2014 in General

 

Tags: , , ,

Cross Reference and Checking for CRUD Operations

Whenever you allow users to modify a list of data from a grid on client side, your server side code can get hairy, trying to perform create / update / delete operations. Part of this is knowing what’s being change, what user has created and deleted, and how we indicate each record.

Common approach could be marking each record the state they are in, for example, when a user creates a new record, the new record is marked as ‘Created’. Then, server-side code handle each one of them based on the state.

Cross reference and checking technique is used to handle this kind of situation. It can be applied to anything that needs create / update / delete at the same time (by that I don’t mean async). I am sure this technique is out there and widely popular already, but for those who haven’t seen it, here it is.

The idea is simple. First, you get all data from database and user input. Then, cross checking them to come up with three lists:

  • What exists only in database
  • What exists only in user input
  • What exists in both

What exists only in database is what needs to be deleted.
What exists only in user input is what needs to be created.
What exists in both is what needs to be updated.

The order in which you perform the operation is not really matter in most cases.

C# Linq samples. Note that in below samples, when my data is deleted from database, it’s being soft-deleted (in IsDeleted column). You can perform hard delete if you wish, the options are not related to this technique.

Get All Data

// Get all data from database
var accountsInDb = _context.GetAllAccounts();
// Get all user input data
var accountsInModel = model.Accounts;

Delete operation

var onlyExistInDb = accountsInDb.Except(accountsInModel).ToList();

onlyExistInDb.ForEach(x => _context.Remove(x));

_context.SaveChanges();

Create operation

var onlyExistInModel = accountsInModel.Except(accountsInDb).ToList();

onlyExistInModel.ForEach(x => {
    _context.Add(new Account {
        AccountId = x.Id,
        AccountName = x.Name,
        AccountRefNumber = x.RefNumber
    });
});

_context.SaveChanges();

Update operation

var existInBoth = accountsInModel.Intersect(accountsInDb).ToList();

existInBoth.ForEach(x => {
    var _account = _context.Accounts.Where(y => y.AccountId == x.AccountId);
    _account.Name = x.Name;
    _account.RefNumber = x.RefNumber;
});

_context.SaveChanges();
 
Leave a comment

Posted by on March 14, 2013 in General

 

Tags: , , ,

LINQ Select Distinct in Flat Data

Have a flat data schema, such as

</pre>
public class Account {
    public int ID {get;set;}
    public string Name {get;set;}
    public int UserID {get;set;}
}

If I have a list of this flat data, sometime it’s necessary to get unique data. This is how I go about doing it

var nonUnique = new List<Account>{
    new Account {
        ID = 1,
        Name = "A",
        UserID = 1
    },
    new Account {
        ID = 1,
        Name = "A",
        UserID = 2
    },
    new Account {
        ID = 2,
        Name = "B",
        UserID = 4
    }
};

var unique = (from m in nonUnique select new { Id = m.ID, Name = m.Name }).Distinct();

Console.Write(nonUnique);
Console.Write(unique);

unique variable contains distinct data of non-unique.

The output is following:

linq-select-distinct-in-flat-data

 
Leave a comment

Posted by on February 20, 2013 in General

 

Tags: ,

Distinct in LinqJS

I was fiddling around Distinct() function in LinqJS, a library to emulate LINQ functions in JavaScript. If you haven’t played with it, head to http://linqjs.codeplex.com/

Anyway, the Distinct() function is little different than one in C#. In LinqJS, you would pass in the predicate to the Distinct() function while in C#, you would call Distinct() after another method. Here’s the example:

C#

var u = dataArray.Select(x => x.AccountID).Distinct();

LinqJS


// Get non-duplicate objects.
var u = Enumerable.From(dataArray).Distinct(function (x) { return x.AccountID; }).Select(function (a) { return a; }).ToArray();

 
Leave a comment

Posted by on January 16, 2013 in General

 

Tags: ,

Impedance Mismatch

In object oriented programming, there is one problem called Impedance Mismatch. The problem occurs when there is a mismatch / differences / gap between objects in an application and representation of data in storage.

Some differences that can cause Impedance Mismatch are:

  • Data type
  • Structural and integrity
  • Manipulative
  • Transactional

Features in programming language and framework can help reduce Impedance Mismatch, such as LINQ and Entity Framework in .Net framework.

Reference: Wikipedia

 
Leave a comment

Posted by on March 6, 2012 in General

 

Tags: , ,

Using Custom Method in LINQ To Entity Framework (LINQ Method Cannot be Translated into a Store Expression)

I got this error when using a custom method in Linq to Entity Framework:

Here’s my code:

using (var context = DataObject.CreateContext())
{
    var _credits = context.CreditEntities;

    var _creditsSorted = _credits.AsQueryable().OrderBy(sortExpression, null);

    return _creditsSorted.Select(x => Mapper.Map(x)).ToList();
}

The error occurs on line 7.

The problem is caused by calling Mapper.Map() from the object that doesn’t recognize (or can’t implement) it, in this case Entity Framework (LINQ to Entity Framework).

To solve the problem, I can’t use Entity Framework to call my custom method. So, the object needs to be converted. In my case (line 3):

var _credits = context.CreditEntities.ToList();
 
Leave a comment

Posted by on July 31, 2011 in General

 

Tags: , , ,

Use ViewModel (Model Binder) in LINQ to Entity Framework – LINQ Doesn’t Return All Joined Data

When using Entity Framework, I’ve found that the join LINQ query that I specified will not return all the data I needed.

Although the query includes OpmaDB.Property entity, the entity is not included in the result object. So, statement like:

_propertyList.FirstOrDefault().Property.PropertyId

will return the famous-‘Object reference not set to an object of an instance’-error:

To resolve this issue, we can use ViewModel, or Model Binder, instead of the entity itself. This should be the better practice anyway. The query looks like:

And the ViewModel looks like this:

Alternatively, we can also store the whole entity model in the View Model instead of property members of the entity. This is how I would do it, the ViewModel:

And the query:

 
Leave a comment

Posted by on March 21, 2011 in General

 

Tags: , , , ,

 
%d bloggers like this: