Not In Clause in LINQ

Consider following code sample where we want to exclude all the Videos which are in dataset from List of videos we have.

For illustration we will create class as follows

public class Video

{

    private int _id;

    public int Id

    {

        get { return _id; }

        set { _id = value; }

    }

    private string _name;

    public string Name

    {

        get { return _name; }

        set { _name = value; }

    }

    private string _url;

    public string Url

    {

        get { return _url; }

        set { _url = value; }

    }

}

 

Now we will write two methods first one “GetVideos” which will emulate fetching video list from certain repository.

    public List<Video> GetVideos()

    {

        Video v1 = new Video { Id = 1, Name = "Video1", Url = "http://www.google.co.in" };

        Video v2 = new Video { Id = 2, Name = "Video2", Url = "http://www.google.co.in" };

        Video v3 = new Video { Id = 3, Name = "Video3", Url = "http://www.google.co.in" };

        Video v4 = new Video { Id = 4, Name = "Video4", Url = "http://www.google.co.in" };

        Video v5 = new Video { Id = 5, Name = "Video5", Url = "http://www.google.co.in" };

        Video v6 = new Video { Id = 6, Name = "Video6", Url = "http://www.google.co.in" };

        Video v7 = new Video { Id = 7, Name = "Video7", Url = "http://www.google.co.in" };

        Video v8 = new Video { Id = 8, Name = "Video8", Url = "http://www.google.co.in" };

        Video v9 = new Video { Id = 9, Name = "Video9", Url = "http://www.google.co.in" };

        Video v10 = new Video { Id = 10, Name = "Video10", Url = "http://www.google.co.in" };

        List<Video> Videos = new List<Video>();

        Videos.Add(v1);

        Videos.Add(v2);

        Videos.Add(v3);

        Videos.Add(v4);

        Videos.Add(v5);

        Videos.Add(v6);

        Videos.Add(v7);

        Videos.Add(v8);

        Videos.Add(v9);

        Videos.Add(v10);

        return Videos;

    }

 While second function “GetDataset” is having list of videos that need exclusion.

    public DataTable GetDataset()

    {

        DataTable dt = new DataTable();

        DataColumn VideoId = new DataColumn("VideoId", typeof(System.Int64));

        DataColumn VideoName = new DataColumn("Name", typeof(System.String));

        DataColumn Url = new DataColumn("Url", typeof(System.String));

        dt.Columns.Add(VideoId);

        dt.Columns.Add(VideoName);

        dt.Columns.Add(Url);

        DataRow dr1 = dt.NewRow();

        dr1["VideoId"] = 1;

        dr1["Name"] = "Video1";

        dr1["Url"] = "http://www.google.co.in";

        dt.Rows.Add(dr1);

        DataRow dr2 = dt.NewRow();

        dr2["VideoId"] = 2;

        dr2["Name"] = "Video2";

        dr2["Url"] = "http://www.google.co.in";

        dt.Rows.Add(dr2);

        DataRow dr3 = dt.NewRow();

        dr3["VideoId"] = 7;

        dr3["Name"] = "Video7";

        dr3["Url"] = "http://www.google.co.in";

        dt.Rows.Add(dr3);

        DataRow dr4 = dt.NewRow();

        dr4["VideoId"] = 8;

        dr4["Name"] = "Video8";

        dr4["Url"] = "http://www.google.co.in";

        dt.Rows.Add(dr4);

        DataRow dr5 = dt.NewRow();

        dr5["VideoId"] = 9;

        dr5["Name"] = "Video9";

        dr5["Url"] = "http://www.google.co.in";

        dt.Rows.Add(dr5);

        DataRow dr6 = dt.NewRow();

        dr6["VideoId"] = 10;

        dr6["Name"] = "Video10";

        dr6["Url"] = "http://www.google.co.in";

        dt.Rows.Add(dr6);

        return dt;

    }

Really LINQ Stuff is here:

In our your page load (say)  we will write linq query to read video list but after exclusion

 var v3 = from m in vids

                 where (

                     from d in dt.AsEnumerable()

                     select d["VideoId"].ToString()

                 ).Contains(m.Id.ToString()) ==false

                 select m;

 

where inner query below

                     from d in dt.AsEnumerable()

                     select d["VideoId"].ToString()

is selecting only Video id’s as string from DataTable that need to be excluded

one can see final result by binding linq output to  gridview.

steps through which we can go to result:Usually we are more concerned in final result than process that derives the solution,here how i made my way to solution,

Here is Complete Code of my page load event:

   if you want to use below code then make sure that in your aspx page there are required three grid views,I first make sure that my inner query give desired result then simply  merged two queries.

        DataTable dt = GetDataset();
        List<Video> vids = GetVideos();

        var v2 = from d in dt.AsEnumerable()
                 select d["VideoId"].ToString();
        GridView1.DataSource = v2;
        GridView1.DataBind();

        //intermediate query
        var v1 = from m in vids
                 select m;
        GridView2.DataSource = v1;
        GridView2.DataBind();

        //final merged query
        var v3 = from m in vids
                 where (
                     from d in dt.AsEnumerable()
                     select d["VideoId"].ToString()
                 ).Contains(m.Id.ToString()) ==false
                 select m;
        GridView3.DataSource = v3;
        GridView3.DataBind();