Dailycode.info

Short solution for short problems

LINQ: Left outer join (non relational DB)

If you want to get data en detail data, from for example Routes. You will need to select the data and the details and then select only the distinct records. If your model framework support relations (Foreing key) then it easier to accomplish with LINQ. But in my case the relations were not set in the object model.

So this is how I get all routes with a detail that have a certain status, or routes that have no details:

var routes = from r in Ents.Routes

                from rd in Ents.RouteDetails.Where(s => s.RouteID == r.RouteID).DefaultIfEmpty()

                where (rd.Status == 10 || rd.Status == null)

select r;

 

var distinctRoutes = routes.Distinct<Routes>();

I cannot use the join with LINQ because there are no relations between the objects, so I select all routes with details that have status 10 or routes that have no details. The .DefaultIfEmpty will add an empty RouteDetail object to the list if it has no detail. So in the were I can check for null, then it includes the Routes that have no details. Now there can be duplicates because some routes have more detials. If we only select the Routes (select r) then the object with different details have the same Route object, so the distinct at the end will clear the duplicates.

 


.Net: using dynamic columns in orderby with LINQ

I have a LINQ query that uses dynamic order by columns. The web client passes an array of key value objects and based on these objects I want to order the LINQ query.

After some googling around I found that the System.Linq.Dynamic namespace has the solution to this!

Add the Linq.Dynamic using the nuget package manager.

Then add the namespace to your class:

using System.Linq.Dynamic;

Now I dynamically build the order by string:

string orderby = String.Join(", ", orderByList.Select(o => o.Key + " " + o.Value ));

For example the orderby string holds this value “SalesOrder DESC CustomerName DESC”. Next we just add the string to the orderby, because you added the namespace in your class, the extensions is available and you can pass a string to the orderby:

List<PlanOrders> lst = PlanEnts.PlanOrders.Where(p => (p.PlanStatus < 100)).OrderBy(orderby).ToList();


LINQ: Group by multiple properties and select only max per group.

I want to get some records from an existing list. The records have to be delivered=false, then from these records I only want the record with the maximum sales order position.

The records could look like this: (ShipID,  SalesOrder, created, createdby, modified, modifiedby)

8431  35602577830  2014-10-15 08:48:29.750  Deraeve Mark @ CORDOBA  2014-10-15 08:57:30.543  MDeraeve@GPA.MES.009

8431  35602577840  2014-10-15 08:48:35.497  Deraeve Mark @ CORDOBA  2014-10-15 08:57:30.553  MDeraeve@GPA.MES.009

8432  24500068130  2014-10-15 09:02:54.577  Deraeve Mark @ CORDOBA  2014-10-15 09:03:45.313  MDeraeve@GPA.MES.009

8433  35602579130  2014-10-15 09:03:13.167  Deraeve Mark @ CORDOBA  2014-10-15 09:03:40.630  MDeraeve@GPA.MES.009

8434  35602583730  2014-10-15 14:32:22.790  Deraeve Mark @ CORDOBA  2014-10-15 14:32:22.790  Deraeve Mark @ CORDOBA

8434  35602583740  2014-10-15 14:32:22.803  Deraeve Mark @ CORDOBA  2014-10-15 14:32:22.803  Deraeve Mark @ CORDOBA

So I only want the get these records at the end:

8431  35602577840  2014-10-15 08:48:29.750  Deraeve Mark @ CORDOBA  2014-10-15 08:57:30.553  MDeraeve@GPA.MES.009

8432  24500068130  2014-10-15 09:02:54.577  Deraeve Mark @ CORDOBA  2014-10-15 09:03:45.313  MDeraeve@GPA.MES.009

8433  35602579130  2014-10-15 09:03:13.167  Deraeve Mark @ CORDOBA  2014-10-15 09:03:40.630  MDeraeve@GPA.MES.009

8434  35602583740  2014-10-15 14:32:22.790  Deraeve Mark @ CORDOBA  2014-10-15 14:32:22.803  Deraeve Mark @ CORDOBA

Now the LINQ query how to get this:

public List<GP_MES_ShippingToDo> GetShippingToDoOrders()

{

var query = Ents.GP_MES_ShippingToDo.Where(s => s.Delivered == false)

                .GroupBy(x => new { x.ShipID, x.SalesOrder })

                .Select(group => group.Where(x => x.SalesOrderPos == group.Max(y => y.SalesOrderPos))

                 .FirstOrDefault());

return query.ToList();

}

 

 


Using LINQ to concatinate an object property from a list of objects

Just put this in a little demo to make it clear. I created a simple object Person, with ID, FirstName and LastName.

Now I fill a List with 3 persons (later I'll fill it with 20000 to do a performance check):

List<Person> persons = newList<Person>();

{

    newPerson(){ID=0, FirstName="Mark", LastName="Deraeve"},

    newPerson(){ID=1, FirstName="Priscilla", LastName="Lauwerijssen"},

    newPerson(){ID=2, FirstName="Anne-Lisa", LastName="Deraeve"}

};

Instead of looping over the list and concatinating it, like this:

start = DateTime.Now;

foreach (Person p in persons)

{

    sPersons = String.Format("{0},{1}", sPersons, p.FirstName);

}

//remove front ,

Console.WriteLine("With Loop: " + sPersons);

I can also just use 1 line to accomplisch this:

Console.WriteLine("With LINQ: " +string.Join(",", persons.Select(p => p.FirstName)));

Result:

 

Now let's say you do not want all records, this makes it even more easy, just put a Where before the Select and you got it!

Let's only show person named 'Deraeve':

Console.WriteLine("With LINQ: " +string.Join(",", persons.Where(p => p.LastName == "Deraeve").Select(p => p.FirstName)));

Result:

 

Now last, lets try to see performance difference. I'll fill the list with 20000 persons. This is where the power comes in. (The number 188889 or 188890(+1 for the extra comma) is the length of the result string)

 

Amazingly, the LINQ only uses 3 or 4 milliseconds, the old for loop takes 1-3 (There is a difference when using String.Format (slower) or just "string + ','+ "string") seconds. This can really make a big difference in today's apps.

So here the complete code so you can test it yourself:

Code of the console app:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace TestConsoleApp

{

    classProgram

    {

        staticvoid Main(string[] args)

        {

            List<Person> persons = newList<Person>();

            /*{

                new Person(){ID=0, FirstName="Mark", LastName="Deraeve"},

                new Person(){ID=1, FirstName="Priscilla", LastName="Lauwerijssen"},

                new Person(){ID=2, FirstName="Anne-Lisa", LastName="Deraeve"}

            };*/

 

            for (int i = 0; i < 20000; i++)

            {

                persons.Add(newPerson() { ID = i, FirstName = string.Format("Name{0}", i), LastName = "Deraeve" });

            }

 

            string sPersons="";

            DateTime start = DateTime.Now;

            TimeSpan ts;

 

            start = DateTime.Now;

            sPersons = string.Join(",", persons.Select(p => p.FirstName));

            ts = DateTime.Now.Subtract(start);

            Console.WriteLine("With LINQ: " + sPersons.Length);

            Console.WriteLine(String.Format("Seconds:{0} Miliseconds{1} ", ts.Seconds, ts.Milliseconds));

            Console.WriteLine("");

 

            sPersons = "";

 

            start = DateTime.Now;

            sPersons = string.Join(",", persons.Where(p => p.LastName == "Deraeve").Select(p => p.FirstName));

            ts = DateTime.Now.Subtract(start);

            Console.WriteLine("With LINQ and Where: " + sPersons.Length);

            Console.WriteLine(String.Format("Seconds:{0} Miliseconds{1} ", ts.Seconds, ts.Milliseconds));

            Console.WriteLine("");

 

            sPersons="";

 

            start = DateTime.Now;

            foreach (Person p in persons)

            {

                sPersons = String.Format("{0},{1}", sPersons, p.FirstName);

            }

            ts = DateTime.Now.Subtract(start);

 

            Console.WriteLine("With for loop: " + sPersons.Length);

            Console.WriteLine(String.Format("Seconds:{0} Miliseconds{1} ",ts.Seconds,ts.Milliseconds));

            Console.WriteLine("");

 

            sPersons = "";

 

            start = DateTime.Now;

            foreach (Person p in persons)

            {

                if (p.LastName == "Deraeve")

                {

                    sPersons = String.Format("{0},{1}", sPersons, p.FirstName);

                }

            }

 

            ts = DateTime.Now.Subtract(start);

 

            Console.WriteLine("With for loop and if: " + sPersons.Length);

            Console.WriteLine(String.Format("Seconds:{0} Miliseconds{1} ", ts.Seconds, ts.Milliseconds));

            Console.WriteLine("");

 

            sPersons = "";

 

            start = DateTime.Now;

            foreach (Person p in persons)

            {

                sPersons = sPersons + ',' + p.FirstName;

            }

            ts = DateTime.Now.Subtract(start);

 

            Console.WriteLine("With for loop without format: " + sPersons.Length);

            Console.WriteLine(String.Format("Seconds:{0} Miliseconds{1} ", ts.Seconds, ts.Milliseconds));

            Console.WriteLine("");

 

            Console.ReadKey();

        }

    }

}

Code of the Person class:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace TestConsoleApp

{

    publicclassPerson

    {

        publicstring FirstName { get; set; }

 

        publicstring LastName { get; set; }

 

        publicint ID { get; set; }

 

    }

}

Things even get better for the Join statement when we add the last name to the result:

sPersons = string.Join(",", persons.Where(p => p.LastName == "Deraeve").Select(p => p.FirstName + ' ' + p.LastName));

VS

foreach (Person p in persons)

{

    sPersons = String.Format("{0},{1} {2}", sPersons, p.FirstName, p.LastName);

}


Result: 



iOS: UITable view (Server Side) Lazy Loading

I'm creating an app that will be used in factories. At the test factories, the application runs fast and smooth. But when starting to deploy to other factories where the workflow is different, I had some grids with loading times of 5 to 7 seconds. They didn't liked it and so did I. So then I had to build in paging in the grids. Since I'm working with a .Net backend server, I will post this answer using LINQ on the server and iOS on the app.

The solution is to show the first 50 elements in the grid, when the 50st element is shown, he has to get the next 50 element and so on... The implementation is actually very simple. I pass the itemCount and the pageSize to the server. So you will need to add these arguments to the getter of your data. On server side I just add a simple Skip and Take using link. Skip the first ... items and Take the (pageCount) number of items. Thats it. So it took me 30 minutes to implement it for the first grid, the next will be 5 minutes of work. 

First on the app side, add this variable

int iPageSize=0;

Then in the CellForRowAtIndexPath method you''l need to check the number of items and load the following items before you return the cell. The store.PageItemCount is a global variable that defines the max items per page, I made it global so it can be used on all grids and variable so we can change the item size on the device.

int indexRow = indexPath.row+1;

int iPageSizeSum = iPageSize+store.PageItemCount;

if (indexRow >= iPageSizeSum)

{

    iPageSize = iPageSize + store.PageItemCount;

    [self configureView];

}

 

in the configureView I add the items to a mutable array and reload the table:

    

[self.ordersOnJig addObjectsFromArray:(NSMutableArray *) value];

[self.locationsTable reloadData];

[self.locationsTable reloadInputViews];

Now the server side integration is even more simple:

returnList = returnList.Skip(skip).Take(pageSize).ToList();

return returnList;



.Net Selftracking entities loose context after serialization.

You can encounter lots of weird problems when you are working with self tracking entities that loose their context. Certainly when you try to save. Insert, delete and selects will not really depend on their context. But saving can give you problems:

For example, when you send the entity over WCF and change something on a non .Net client. Sometimes he will just save it. But sometimes you will get the error that an object with that key is already in the context. Or a unique key constraint or whatever. I tried several things to solve it. Many tries where good, but none was stable and perfect. 

Now I find I've found a trustworthy and stable solution. I got to it when testing al kinds of automapper implementations. The goal was to map the entity's properties that was decoupled from the context with the entity that was in the context. But very soon I noticed that most automapper just create a new instance of the object and map the properties to this. So that was no help. What I needed was an automapper that would map the properties of one instance to the properties of another instance (the one form the context) and not a new instance with comined properties. So I found mapper code that did the trick here. I'm using the second implementation of the cached property maps. Not using the static implementation, but rather a Singleton. So I can put the initial creation of all mappings in the constructor. To map the object coming from the webservices to the object from the context is simple:

//Get the object from the context

GalvaSFIMobileEntities.GP_MES_F06ByOrder fc = PL.GetF06OrderInfo(f06.SalesOrder);

//Map the object's properties to the properties of the context object

GenericSingleton<SelfTrackingObjectMapper>.GetInstance().CopyMatchingCachedProperties(f06, fc);

//Save the object from the context with the matched proprties.

PL.SaveF06ByOrder(fc);

Now for the implementation of this mapper. Just add a class to your project and past this code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Reflection;

using System.Text;

using Microsoft.CSharp;

 

public class PropertyMap

{

 

    public PropertyInfo SourceProperty { get; set; }

 

    public PropertyInfo TargetProperty { get; set; }

 

}

 

 

///<summary>

/// Summary description for SelfTrackingObjectMapper

///</summary>

public class SelfTrackingObjectMapper

{

    public SelfTrackingObjectMapper()

    {

        //

        // TODO: Add constructor logic here

        //

        AddPropertyMap<GalvaSFIMobileEntities.GP_MES_F06ByOrder, GalvaSFIMobileEntities.GP_MES_F06ByOrder>();

        AddPropertyMap<GalvaSFIMobileEntities.GP_MES_F06ByOrderDetails, GalvaSFIMobileEntities.GP_MES_F06ByOrderDetails>();

    }

 

    public IList<PropertyMap> GetMatchingProperties(Type sourceType, Type targetType)

    {

        var sourceProperties = sourceType.GetProperties();

        var targetProperties = targetType.GetProperties();

 

        var properties = (from s in sourceProperties

                          from t in targetProperties

                          where s.Name == t.Name &&

                                s.CanRead &&

                                t.CanWrite &&

                                s.PropertyType.IsPublic &&

                                t.PropertyType.IsPublic &&

                                s.PropertyType == t.PropertyType &&

                                (

                                  (s.PropertyType.IsValueType &&

                                   t.PropertyType.IsValueType

                                  ) ||

                                  (s.PropertyType == typeof(string) &&

                                   t.PropertyType == typeof(string)

                                  )

                                )

                          select new PropertyMap

                                     {

                                         SourceProperty = s,

                                         TargetProperty = t

                                     }).ToList();

        return properties;

    }

 

 

    private Dictionary<string, PropertyMap[]> _maps =

    new Dictionary<string, PropertyMap[]>();

 

 

    public void AddPropertyMap<T, TU>()

    {

        var props = GetMatchingProperties(typeof(T), typeof(TU));

        var className = GetClassName(typeof(T), typeof(TU));

        _maps.Add(className, props.ToArray());

    }

 

 

 

    public void CopyMatchingCachedProperties(object source, object target)

    {

        var className = GetClassName(source.GetType(),target.GetType());

        var propMap = _maps[className];

 

        for (var i = 0; i < propMap.Length; i++)

        {

            var prop = propMap[i];

            var sourceValue = prop.SourceProperty.GetValue(source, null);

            prop.TargetProperty.SetValue(target, sourceValue, null);

        }

    }

 

    public string GetClassName(Type sourceType,Type targetType)

    {

        var className = "Copy_";

        className += sourceType.FullName.Replace(".", "_");

        className += "_";

        className += targetType.FullName.Replace(".", "_");

        return className;

    }

}

I choose to keep creation of the mappings based on the properties of both objects. I could in my case only use the properties of the frist object, since they should be the same, but the mappings are only created once and maybe in the future I could use this for other purposes. Eg. POCO -> Self tracking entities. 

Last thing you will need is the GenericSingleton class, you can find this on google, but for the sake of completeness:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace GP_Global

{

    public class GenericSingleton<T> where T : class, new()

    {

        private static T instance;

       

        public static T GetInstance()

        {

            lock (typeof(T))

            {

                if (instance == null)

                {

                    instance = new T();

                }

                return instance;

            }

        }

    }

}


Generic List Intersect VS LINQ Query

 

I needed to intersect 2 Generic List with some class objects in the lists. The 2 generic lists had the same type. The objects were self-tracking entities, that is why (I guess) the Intersect would work using the default comparer. The definition of the intersect without writing a custom comparer is: Produces the set intersection of two sequences by using the default equality comparer to compare values. So I tried a different solution using the LINQ query. Just joining the 2 lists.

List<MyEnt> favEnts = PL.GetFavoritEnts(UserID).ToList();

List<MyEnt> allEnts = PL.GetAllEnts().ToList();

 

First the intersect that wouldn’t work:, it returned 0 Ents in the returned list.

dataSource = allEnts.Intersect(favEnts).ToList();

 

Next the solution that worked fine:

var query = from a in allEnts

            join b in favEnts on a.EntID equals b.entID

            select a;

dataSource = query.ToList();

 

The query.ToList() return a list of type List<MyEnt>


Entity framework and LINQ: load related tables

I was trying to load data coming from a linked table using a link table to manage the link records.
The entity diagram looks like this:
 
 
 
I’m using self-tracking entities. I’m loading the data from the Program table and need to know which transformants are associated with the program.
So here’s the LINQ code that I needed:
var list = from d in ents.Programs.Include("ProgramTransFormants.Transformants")
                       orderby d.Description
                       select d;
 
So with the linktablename.tablename the entity framework will load the child elements in one call.
 
 
 

Media control database version 1

I created a small WPF application that can handle all your hard copy media like CD's, DVD's, Blue ray, records, books etc.

It wil lhelp you keep track of things when you lend out media and find them.

See more of this here.

If you like to get the source code to see the WPF/MVVM code, no problem, simply contact me and I'll provide it.

Data is stored in XML. I used linq to query the data and WPF and MVVM to show the data.