Uncategorized – Page 19

Paging and the Entity Framework, Skip, and Take Part 3

Get the total count of pages. | Get the real page number. | Using Skip and Take to Page | The Actual Paging Controls

Ok so the last two posts have been arguably useless, maybe more so than anything else here, but they were somewhat needed because now I am going to show how to Linq, the Entity Framework, and well that’s it I think.

public static IList<ToolItem> GetSomeTools(String name, Int32 numberToShow, Int32 pageNumber, out Int32 realPage, out Int32 totalCountOfPages)
{
  //EntityContext.Context is just a singletonish version of the
  //Entities class.  Most people would use
  //  using (ToolEntities context = new ToolEnties())
  Int32 totalCount = EntityContext.Context.ToolItems
		   .Count(item => item.Name == name);
  //This is the method from the first post of this series
  //Just getting the count of pages based on numberToShow and
  //item totalCount
  totalCountOfPages = TotalCountOfPages(totalCount, numberToShow);
  //This is the method from the second post of this series
  //Basically getting the best possible page if the page number
  //is higher than the totalCountOfPages or lower than 0
  realPage = GetRealPage(totalCountOfPages, pageNumber);

  returnValue = EntityContext.Context.ChatRooms
			  .Where(item => item.Name == name )
			  .OrderBy(item => item.Name)
			  .Skip(numberToShow * realPage)
			  .Take(numberToShow)
			  .ToList();

  return returnValue.ToList();
}

Really simple yes? It follows like this:

Say I’m on page 1, which for this would be zero or pageNumber – 1. So I want to grab the first 20 items from the database. Well that means I want to start at 0 and grab 20. Now if you want this all to be done with some kind of conditional thing that either handles the first page or the other pages, you actually want to skip the same way no matter what the page number is. This is taken care of by numberToShow * realPage since even at 0 this works. After all 0 * anything is 0 and therefore you will be Skipping 0 items. So in other words, you’re at the start. Next you want to Take the amount of items you need, which is 20. Next time around you’ll start at 20 Skip(numberToShow 20 * realPage 1) and take the next 20. Nice thing is, even if you say Take 20 and there are only 2 left, it doesn’t care. It will just grab those two.

And there you have it, how to page with the Entity Framework and minimal amount of work. I know I hate taking other people’s methods (Like the TotalCountOfPages and GetRealPage methods), don’t know why. So sorry if I am forcing you to do so. However, the two methods I gave are semi important to this.

You might wonder why realPage and totalCountOfPages, well this is useful stuff when knowing what page is next for paging controls. Next post I’ll show those off but I’ll warn you, they are nothing spectacular.

Paging and the Entity Framework, Skip, and Take Part 2

Get the total count of pages. | Get the real page number. | Using Skip and Take to Page | The Actual Paging Controls

So in part one I posted the method to find the total count of pages you’ll need so that you don’t go too far while paging. Now it’s about trying to get the best possible page number despite what’s passed in.

Here’s the situation, when you are paging up or down, you want to make sure you don’t go lower than 0 or higher than whatever the total count of pages is. Sometimes though, things go wrong. They go very wrong. It’s possible that your ui design will allow for any number to be passed in as the page number (Too many reasons this could happen to bother with). Say you have only 10 possible pages but the number 12 gets passed in. Well you either allow it to grab a non existant page 12 or you have a method to determine the best possible choice at this point (10). Turns out it’s idiot simple… what? Were you expecting anything different?

public static Int32 GetRealPage(Int32 totalCountOfPages, Int32 pageNumber)
{
  //pageNumber and totalCountOfPages have to be above 0 or problems
  if (pageNumber > 0 && totalCountOfPages > 0)
  {
    //If page number is higher than the possible count of pages,
    //then you just need the total count to be the new page number...
    //but there's one more step
    pageNumber = totalCountOfPages < pageNumber ? totalCountOfPages : pageNumber;
    //If by chance the pageNumber now is the same as totalCountOfPages
    //(Meaning it was larger than totalCountOfPages originally)
    //1 has to be subtracted to make sure it's inline with a 0 based system where
    //page 1 is actually 0.  This will make more sense when using skip and take in
    //the next post.
    pageNumber = totalCountOfPages != pageNumber ? pageNumber : totalCountOfPages - 1;
  }
  else
  {
    pageNumber = 0;
  }

  return pageNumber;
}

Ok so I’m going to claim this is amazing stuff, but it will come in handy with the next post.

Paging and the Entity Framework, Skip, and Take Part 1

Get the total count of pages. | Get the real page number. | Using Skip and Take to Page | The Actual Paging Controls

So something I’ve been doing a lot of lately is making quite possibly the best thing ever: String cheese wrapped in turkey bacon. But when I’m not doing that, I am working with a lot of ListViews and the entity framework. Now I know there are “built in” paging controls but I just haven’t liked what I’ve seen. So I took it upon myself one day to develop a repeatable system for paging. Say you have users and a bunch of invites that are attached to the users. You want to show a list of invites for a particular user, but you want a ListView that doesn’t show every invite… ie you need a pager. Well just so happens I have a solution, but as always you should consult a doctor before use.

First off you need a method to get the total count of pages meaning what the ceiling is when you page upwards. After all, if you go over the possible count of pages, you’ll just get no items returned and look kind of dumb.

There are three situations you have to look out for: You have less items that the required items per page (Say 10 rows but you display 20 row per page), you have a perfect division (20 rows and 20 row per page or 40 rows and 20 rows per page, ect), you have an uneven division (21 rows and 20 rows per page). First two are easy, third isn’t hard exactly but there are some catches.

public static Int32 TotalCountOfPages(Int32 rowCount, Int32 neededPageSize)
{
  Int32 returnValue;
  if(rowCount > 0)
  {
    if (rowCount <= neededPageSize)
    {
      returnValue = 1;
    }
    else
    {
      if ((rowCount % neededPageSize) == 0)
      {
        returnValue = rowCount / neededPageSize;
      }
      else
      {
        Decimal convertedPageSize =
         Convert.ToDecimal(neededPageSize);
        Decimal convertedRowCount =
         Convert.ToDecimal(rowCount);
        Decimal resultRounded =
          Math.Round(convertedRowCount / convertedPageSize);
        Decimal resultNonRounded =
          convertedRowCount / convertedPageSize;

        if (resultRounded < resultNonRounded)
        {
           returnValue =
            Convert.ToInt32(resultRounded + 1);
        }
        else
        {
           returnValue =
            Convert.ToInt32(resultRounded);
        }
      }
    }
  }
  else
  {
    returnValue = 0;
  }
  return returnValue;
}

Ok so first off, I assume this one is pretty obvious:

  if(rowCount > 0)

If there aren’t any rows, the there can’t be a page count.

Next take care the less rows than being shown per page:

  if (rowCount <= neededPageSize)
  {
    returnValue = 1;
  }

Simple enough. Now for the second part, a perfect division between rows and rows to show:

 if ((rowCount % neededPageSize) == 0)
 {
    returnValue = rowCount / neededPageSize;
 }

Basically, for those who don’t know mod or the % operator, that means there is no remainder. If there were, the result of rowCount % neededPageSize would not be 0 since Mod basically means “Give me what’s left over when I divide something by something else.”

Ok, this is where it gets a little messy as I have yet to find a good way to round a number up since, far as I know, there’s no way to do it with the .Net lib’ary. So, I had to come up with something clever… and when that failed I came up with this:

 Decimal convertedPageSize =
    Convert.ToDecimal(neededPageSize);
 Decimal convertedRowCount =
    Convert.ToDecimal(rowCount);
  Decimal resultRounded =
    Math.Round(convertedRowCount / convertedPageSize);
  Decimal resultNonRounded =
    convertedRowCount / convertedPageSize;

  if (resultRounded < resultNonRounded)
  {
      returnValue =
          Convert.ToInt32(resultRounded + 1);
  }
  else
  {
     returnValue =
       Convert.ToInt32(resultRounded);
  }

Ok so what’s going on here? First off, because trying to divide an integer by an integer gives me an integer, I had to covert some stuff to decimal. Why is that? When I divide them, I need to know if the number after the decimal is between 1 and 1.5 since Math.Round will round down in that case killing my ability to tell if I have enough pages.

Example: 19 rows and 10 per page will give me a 1.9 which Round will make 2. This is perfect since I need two pages to show the 19 rows. What if I have 14 rows and 10 per page? I get 1 from rounding. Uh oh. I really need two pages.

How do I get around this? Get the rounded version and the unrounded version. From there I know that if the unrounded version is greater than the rounded version I need an extra page.

So 14 rows / 10 per page = 1.4 unrounded and 1 rounded. 1.4 > 1, so that 1 page isn’t going to do it. I need to add one more page. Now if it 19 rows/10 per page I’ll get 1.9 unrounded and 2 rounded. 2 pages is exactly what I need and I don’t have to add anything.

Next post I’ll show the next needed method: How to tell if the page number being sent in (From the pager/UI) is actually valid. If not, then grab the last possible amount of records.

A relationship is being added or deleted from an AssociationSet … With cardinality constraints, a corresponding … must also be added or deleted.

So I ran into this error yesterday trying to delete a user which in turn has multiple collections that would be deleted too. You would think that I would have to manually delete the entire User tree, on collection at a time. Well the way thing work is that Entity framework will do the work for you if your tables are set to Cascade Delete. Now, you might see that error and be confused as to why it crops up even when you do have Cascade Delete on the needed tables. Here’s the situation:

As I said, I was trying to delete a user through the entity framework but noticed that all the addresses the user had were still in the database after deletion. Whoops. I dun forgot to set Cascade Delete on the tables. No big deal. So after I fixed the tables, I went to update the edmx file… ie update from database… and I thought all was well. Yeah not so much. I started to get this error. So the next thing I did was open the .edmx file in notepad and looked for “delete”. Sure enough I found it.

        <Association Name="FK_UserAddress_User">
          <End Role="ChatUser" Type="BAT.Data.Store.User" Multiplicity="1"">
            <OnDelete Action="Cascade" /">
          </End">

Eh ok… it’s there. Well after some searching I ran into this post. Basically what was happening is although that shows the OnDelete Action=”Cascade”, it’s still not everywhere it needs to be. Then it dawned on me that the way the .edmx file works is that pretty much everything has to be doubled in the file, once for the database and once for the actual class. What was missing was the class half. For some reason when adding Cascade to a foreign key and then updating the .edmx file, only the table part of the markup gets updated. Bummer. So, what to do? Kind the foreign key name in the file (FK_UserAddress_User for example) and do a search on it. You’ll find something like this:

     <Association Name="FK_UserAddress_User">
           <End Type="BAT.Data.ChatUser" Role="User" Multiplicity="1" />

Oooo right there is the problem. You see, if this were correctly done, it would have the action=”delete” added to it, just like the one in the SSDL area. So how do you fix this? Manually. Hooray.

     <Association Name="FK_UserAddress_User">
           <End Type="BAT.Data.ChatUser" Role="User" Multiplicity="1" >  //RIGHT HERE
             <OnDelete Action="Cascade"></OnDelete>  //RIGHT HERE
           </End>  //RIGHT HERE
     <End Type="BAT.Data.UserAddress" Role="UserAddress" Multiplicity="*" /></Association>

As you can see, it’s a simple change and you only have to do it to the object of Multiplicity=”1″, which of makes sense you wouldn’t want a user deleted if an address is. But it’s still annoying and a real pain if you have more than say one to fix.

Linq Join Extension Method and How to Use It…

I don’t like using the query syntax when it comes to Linq to AnythingButTheKitchenSink . Not sure why. Mostly, I guess, is that I seem to have a liking for Funcs and Actions to the point of stupidity and although you can work them into the query syntax, it just doesn’t look right.

Now with most of the Linq methods like Where or First, it’s simple once you understand lamdba expressions:

.SomeMethod(someField => someField.Property == value);

Now what about join?

So inner selector with an outer selector and a selector selects a selecting selector. Right got it.

Well let’s try to break it down. First part is

this IEnumerable<TOuter>

So being that this is an extension method meaning this is the collection you are using this method on.

IEnumerable<TInner> inner

So second field must be the list you want to join to. Ok so far.

Func<TOuter, TKey> outerKeySelector

Now this is where it gets a little odd looking. We know we have Outer and Inner lists so there needs to be a way to join on something. Say Outer is User and Inner is UserAddress. Most likely you will have a UserID on both lists. If not, you do now. So basically what this part of the method is saying is “Give me the stupid key on the Outer (User) list that I should care about.”

, user => user.UserID,

Next part:

Func<TInner, TKey> innerKeySelector

Pretty much the same thing, except now it needs the key from the Innerlist (UserAddress):

, address => address.UserID,

Now for the fun part:

Func<TOuter, TInner, TResult> resultSelector

Sa…say what? Ok this may look weird at first but you’ll hate yourself for not seeing it. It’s just asking you what to select from the two lists as some kind of hybrid object. See, you have to remember that with these linq methods, each method will produce a list. You can’t just chain them together and have it remember every list you’ve made:

   user.Where(user => user.UserID > 1) // gives me a list of users
         .Select(user => new { user.UserName, user.UserAddress, user.UserID } 
         //Gives me new items with user name, address, and user id

From this simple method chain, the end list is NOT the same as the one you started with or the one produced by the where method.

The last part of the Join method needs you to tell it what it’s going to produce from this join. Now it probably could just guess and include both lists, but that could be seen as sloppy and ultimately this gives you the choice of what exactly needs to be taken after the join. So:

, (user, address) => new { user, address});

So in this case, the newly created and joined list with be a list of items that have a user and address attached to it much like if you had a list of:

class UserAddressHybrid()
{
    public User user { get; set; }
    public UserAddress userAddress { get; set; }
}

So in other words, WHAT DO YOU WANT YOUR RESULTS TO LOOK LIKE?

In full it would look something like:

user.Join(address => address.User.UserID,  //IEnumerable<TInner> inner
             user => user.UserID,  //Func<TOuter, TKey> outerKeySelector
             address => address.UserID,  //Func<TInner, TKey> innerKeySelector
             (user, address) => new { user, address});  //Func<TOuter, TInner, TResult> resultSelector

Not so hard anymore, is it? You can start kicking yourself now.

SPAM OR COMMENT??!?! 2

Hello Man!. Just one more question. Realy, need your help.
Question about Alcohol posioning?

100 times thenks. I am Waiting for answer!!!

Now I might have been fooled at first, but I’m leaning toward spam on this one. I have to think that if you did indeed have a question about alcohol poisoning you might try a medium faster than say comments on a blog. Course by now if he was looking for an answer it probably doesn’t matter anymore.

Or maybe it’s the fact THIS IS A BLOG ABOUT PROGRAMMING. Then again, maybe it was a comment for this post.

SPAM OR COMMENT?!?!?

So here’s the first round of a new game. Really simple, I post the “comment” and you tell me if it’s real or spam.

Hi Man!, please, need your help.
Wat is an alternative to glass pipe screens?

Thenks, bro. I am vaiting for answer!!!

So what’s your take on it?

NHibernate Cross Join: The multi-part identifier … could not be bound..

Something else I found out about NHibernate, at least an older version, and cross joins… and if you’re reading this chances are you have the same version. Say you want to cross join something but there are also other joins. You would think it should be like this:

    SELECT
       user.UserName, status.StatusName
    FROM
       User user, UserStatus status
    LEFT JOIN
      user.Address address
    WHERE
      user.CreatedDate = status.CreatedDate

Ok so once again, this query is kind of stupid but the idea is there. With NHibernate you have to Cross Join when you need to match two objects but not on the mapped keys. Meaning that User and Status might be mapped on UserID ie Status.UserID = User.UserID. But what if you wanted to match them on something else, like a date. Well that’s where the cross join came in. The Sql might look like:

    SELECT
       user0_.UserName, status0_.StatusName
    FROM
       ByATool.User user0_, ByATool.UserStatus status0_
    LEFT JOIN
       ByATool.Address address0_ ON address0_.UserID = user0_.UserID
    WHERE
       user0_.CreatedDate = status0_CreatedDate

Looks fine right? Well it’s not according to SqlServer 2005. Turns out the cross join has to appear AFTER the other joins. So in reality it should look like:

    SELECT
       user0_.UserName
    FROM
       ByATool.User user0_, ByATool.UserStatus status0_
    LEFT JOIN
       ByATool.Address address0_ ON address0_.UserID = user0_.UserID,
       status0_.StatusName
    WHERE
       user0_.CreatedDate = status0_CreatedDate

Which means the hql looks like this:

    SELECT
       user.UserName
    FROM
       User user, UserStatus status
    LEFT JOIN
      user.Address address,
      status.StatusName
   WHERE
      user.CreatedDate = status.CreatedDate

See how the cross joined object is now after the Left Join? Looks odd but this is what it takes to get the cross join through. Kind of annoying.

NHibernate.QueryException: expecting ‘elements’ or ‘indices’ after…

Yes, it’s true. I have two lovers. Well more like I’m stuck with NHibernate due to some kind of stupid forced marriage and the other is my somewhat quirky but ultimately young and hot mistress Entity Framework. And now that I’ve admitted to such an atrocity you are about to run screaming, I’ll get on with what the hell this means.

So I ran across this the other day and well I’m sure a smart person would immediately understand what it means, but that smart person doesn’t write for this blog. You’re stuck with me. So after some staring at the screen and praying to whatever god I just learned about on wikipedia, I figured it out. Now I can’t promise this is the only reason to get this error, but this is the answer I have.

Say you have a user and with that user you have a list of events. Now it should be obvious that there is an event class, an event table, and user table. Simple enough. Now in hql, it would look something like this:

    SELECT
       user
    FROM
       User user
    LEFT JOIN
       user.Events event
    WHERE
       user.Events.Status = SomeStatusEnum.SomeValue

And yes I realize that it doesn’t need to be a left join, but just roll with me on this because I don’t feel like making a better example. So if you look close enough at the query you should see something wrong… Why would I join the objects and then refer to the event status through the user?

       user.Events.Status = SomeStatusEnum.SomeValue

Because I’m a tool. Yeah turns out that, to me, crytic message is really saying “Use the joined object alias you f-ing tool.” In other words:

       events.Status = SomeStatusEnum.SomeValue

And boom. No more error.