How to get what exist in the first datatable and not exist in the second data table in a third one?

Q:

I have two queries each one return a DataTable. I wanna to return another DaTaTable as a result of(What exist in the first DataTable AND Not Exist(NOT IN) the second DataTable).

My queries:

EDIT : I make it general:

1-DT1:

DataTable dt1 = cc1assiscrsevalDAL.GetAll(int.Parse(Session["course_prof"].ToString()), 0);

2-DT2:

DataTable dt2 = cc1assiscrsevalDAL.Ge开发者_StackOverflow社区tConfirmedEval(int.Parse(Session["course_prof"].ToString()));

Note:batch_no,crsnum,lect_code are the composite primary key

What is the best way to do that?(wise performance). I wanna also to do that with LINQ.(if possible).

var dt = dt1.AsEnumerable().Except(dt2.AsEnumerable(), new CustomDataRowEqualityComparer()).CopyToDataTable();

    public class CustomDataRowEqualityComparer: IEqualityComparer<DataRow>
        {

            public bool Equals(DataRow x, DataRow y)
            {
                return ((int)x["crsnum"]) == ((int)y["crsnum"])
                    && ((int)x["crsnum_e"]) == ((int)y["crsnum_e"])
                        && ((int)x["crstteng"]) == ((int)y["crstteng"]);
            }

            public int GetHashCode(DataRow obj)
            {
                return ((int)obj["crsnum"]) ^ ((int)obj["crsnum_e"]) ^ ((int)obj["crstteng"]) ;
            }
        }

There is an extension method in the linq called Except which solves your problem but we need to create a separate class for that which i have done in the above code.

select * 
from (*target_query*) t
join
(
  select batch_no,crsnum,lect_code from (*target_query*) q
  except
  select batch_no,crsnum,lect_code from cc1assiscrseval
) temp on temp.batch_no = t.batch_no and temp.lect_code = t.lect_code, temp.crsnum = t.crsnum 

Pretty dirty solution, but I think you could simplify it by getting only desired batch_no, crsnum and lect_code, without performing first query twice. But you'll have to figure it out yourself.

Linq provides you with an Except method so you could do something like this

var _differences = dt1.AsEnumerable.Except(dt2.AsEnumerable()); // No checked or tested in VS

You could also first get the relevant columns by using something like this:

var x = From a In dt1
    Select (...relevant columns)

var y = From a In dt2
    Select (...relevant columns)

And then do the above except.

HTH!