CODEWITHSUNDEEP
c#linq
10101
10101

Reputation: 2412

Compare 2 Columns in DataTable to 2 Columns in List and build a new List with certain criteria

I have DataTable

public static DataTable SubProjects = new DataTable();

public static DataTable GetSubProjects
{
    get { return SubProjects; }
}

it comes from database so columns are as follows

select ProjectN,ProjectSubN,ProjectM

and list:

public class SubProjectsList
{
    public string ProjectNumber { get; set; }
    public string SubProjectNumber { get; set; }
    public string SubProjectName { get; set; }
    public string ProjectManager { get; set; }
    public int ObjectID { get; set; }

    public SubProjectsList(string ProjectNumber, string SubProjectNumber, string SubProjectName, string ProjectManager, int ObjectID)
    {
        this.ProjectNumber = ProjectNumber;
        this.SubProjectNumber = SubProjectNumber;
        this.SubProjectName = SubProjectName;
        this.ProjectManager = ProjectManager;
        this.SubProjectNumber = SubProjectNumber;
        this.ObjectID = ObjectID;
    }
}

public static List<SubProjectsList> DeliverySubProjectList = new List<SubProjectsList>();

Ultimately I would like to get all results to new List where

ProjectN + ProjectSubN (DataTable) = ProjectNumber + SubProjectNumber (List) && 
ProjectM (DataTable) != ProjectManager (List)

As an output I need to get ProjectNumber, SubProjectNumber, ProjectM, ProjectManager. Basically I need to catch ProjectM and ProjectManager that does not match for one sub-project. I could have done it this way

var SubProjectResult = SubProjects.AsEnumerable()
    .Where(x => DeliverySubProjectList.Any(y => 
    y.ProjectNumber.Trim() + y.SubProjectNumber == x.Field<string>("ProjectN").Trim() + x.Field<string>("ProjectSubN").Trim() &&
    !string.Equals(y.ProjectManager.Trim(), x.Field<string>("ProjectM").Trim(), StringComparison.CurrentCultureIgnoreCase)))
    .Select(x => new
    {
        ProjectN = x.Field<string>("ProjectN"),
        ProjectN = x.Field<string>("ProjectSubN"),
        ProjectM = x.Field<string>("ProjectM"),
        ProjectM = x.ProjectManager // This is not possible
    })
    .OrderBy(o => o.ProjectN).ToList();

but

ProjectM = x.ProjectManager // This is not possible

is wrong and does not compile. So I can't get ProjectManager from List where ProjectN + ProjectSubN (DataTable) = ProjectNumber + SubProjectNumber (List) match but ProjectManager does not match. Do I need to Concat DataTable and List together first? I have tried this

var myList1 = SubProjects.AsEnumerable().Concat(DeliverySubProjectList).ToList();

but getting an error

Severity Code Description Project File Line Suppression State Error CS1929 'EnumerableRowCollection' does not contain a definition for 'Concat' and the best extension method overload 'Queryable.Concat(IQueryable, IEnumerable)' requires a receiver of type 'IQueryable'

Upvotes: 1

Views: 53

Answers (1)

ravi kumar
ravi kumar

Reputation: 1620

I think this should give you what you are looking for. You might need to add extra null checks and I am also not very sure about that's how you want to add string values from the list and the data table:

public class ResultModel
        {
            public string ProjectNumber { get; set; }
            public string SubProjectNumber { get; set; }
            public string ProjectM { get; set; }
            public string ProjectManager { get; set; }
        }

...
...
static void Main(string[] args)
        {
            List<SubProjectsList> DeliverySubProjectList = // populate list
            DataTable SubProjects = // populate datatable
            List<ResultModel> results = GetResults(DeliverySubProjectList, SubProjects).ToList();
        }

        private static IEnumerable<ResultModel> GetResults(List<SubProjectsList> DeliverySubProjectList, DataTable SubProjects)
        {
            return SubProjects.AsEnumerable().Select(row =>
            {
                var listItem = DeliverySubProjectList.FirstOrDefault(item =>
                {
                    return item.ProjectNumber + item.SubProjectNumber == row["ProjectN"].ToString() + row["ProjectSubN "].ToString()
                        && !item.ProjectManager.Equals(row["ProjectM"].ToString());

                });

                if (listItem != null)
                {
                    return new ResultModel
                    {
                        ProjectNumber = row["ProjectN"].ToString(),
                        SubProjectNumber = listItem.SubProjectNumber,
                        ProjectM = row["ProjectM"].ToString(),
                        ProjectManager = listItem.ProjectManager
                    };
                }
                else
                {
                    return null;
                }
            })
            .Where(res => res != null);
        }

Upvotes: 1

Related Questions