Saturday, May 16, 2015

MVC 5 scaffolding Model & Unique Key Contraint

     
    MVC 5 scaffolding allows are to build CRUD functionality on fly.but edit controller generated in such way does not handle unique key violation.So we need to add some code in our part to handle it.

Approach is quite simple first check if record violate unique key constraint in my case "Pancard" field has unique key. if such violation found then we will add validation message error as in below line

  ModelState.AddModelError("Pancard", "Unique key violation !");

My Example Model is :

    public partial class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Email { get; set; }
        public string Cell { get; set; }
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public string Pancard { get; set; }
        public string Pin { get; set; }
    }

Modified Controller:

 [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include="PersonId,Name,City,State,Email,Cell,Address1,Address2,Pancard,Pin")] Person person)
        {
            if (ModelState.IsValid)
            {
                try
                {
                    //check for unique key violation
                    int cnt =(from m in db.People where m.Pancard == person.Pancard select m.Pancard).Count();
                    if (cnt ==0)
                    {
                        db.Entry(person).State = System.Data.Entity.EntityState.Modified;
                        db.SaveChanges();
                    }else{
                         ModelState.AddModelError("Pancard", "Unique key violation !");
                         return View(person);
                    }
                    return RedirectToAction("Index");
                }
                catch (System.Data.DataException ex)
                {
                                    
                    return RedirectToAction("Index");
                }
            }
            return View(person);
        }

Tuesday, February 10, 2015

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();