Home > database >  Push records from one model array to another model array with conditions
Push records from one model array to another model array with conditions

Time:01-18

I have below two models:

TrackList:

public int id { get; set; }
public string name { get; set; }
public int apr_by { get; set; }
public int reg_by { get; set; }

TrackListMain:

public int id { get; set; }
public string name { get; set; }
public string status { get; set; }

Suppose I would get the below records from DB in the model array TrackList by executing:

var dbArray = await _SQLHelper.GetRecords<TrackList>("[TASKLIST]", parameters);
id name apr_by reg_by
1 aaa 1 1
2 bbb 2 null
3 ccc null 3
4 ddd null null

Now I need to push each record to the model array TrackListMain based on these conditions:

foreach dbArray => {
    if (`dbArray.apr_by` == null && `reg_by` == null), push to `TrackListMain` and update `status` field with `null`
    if `dbArray.apr_by` !=null, push to `TrackListMain` and update `status` field with `AB`
    if `dbArray.reg_by` !=null, push to `TrackListMain` and update `status` field with `RB`
}

And my resultant array IEnumerable<TrackListMain> mainArray should contain data with the below format:

id name status
1 aaa AB
1 aaa RB
2 bbb AB
3 ccc RB
4 ddd null

Can you help me to achieve this in C# either by using LINQ?

CodePudding user response:

For LINQ approach,

  1. Filter data by conditions (3 sets) and decorate the output result.

  2. Combine 3 set of data into one with .Union().

  3. Order the result by Id.

List<TrackListMain> result = (dbArray.Where(x => x.Apr_By == null && x.Reg_By == null)
                                 .Select(x => new TrackListMain
                                         {
                                             Id = x.Id,
                                             Name = x.Name,
                                             Status = null
                                         })
                            )
                            .Union(dbArray.Where(x => x.Apr_By != null)
                                 .Select(x => new TrackListMain
                                         {
                                             Id = x.Id,
                                             Name = x.Name,
                                             Status = "AB"
                                         })
                            )
                            .Union(dbArray.Where(x => x.Reg_By != null)
                                 .Select(x => new TrackListMain
                                         {
                                             Id = x.Id,
                                             Name = x.Name,
                                             Status = "RB"
                                         })
                            )
                            .OrderBy(x => x.Id)
                            .ToList();

Demo @ .NET Fiddle

CodePudding user response:

First of all you should make sure how the _SQLHelper behaves if the values in database are null. If it returns null then you need to make the class properties nullable:
TrackList

public int id { get; set; }
public string name { get; set; }
public int? apr_by { get; set; }
public int? reg_by { get; set; }

Otherwise it makes no sense to check them for null in C# (then you would probably check if they are 0 which is int default value).

The query could look like this:

You need this using:
using System.Linq;

var mainArray = dbArray.Select(x => new TrackListMain
{
    id = x.id,
    name = x.name,
    status = x.apr_by == null ? (x.reg_by == null ? null : "RB") : "AB"
});

It is not very readable tho, so maybe better go with a method:

var mainArray = dbArray.Select(ConvertTrackList);
private static TrackListMain ConvertTrackList(TrackList trackList)
{
    var main = new TrackListMain()
    {
        id = trackList.id,
        name = trackList.name
    };
    if (trackList.apr_by != null)
        main.status = "AB";
    else if (trackList.reg_by != null)
        main.status = "RB";

    return main;
}
  • Related