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 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 records 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 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;
}