The only Windows 7 compatibility problem I have had was with Nero Back-It-Up 4. It doesn't seem to recognize the Windows Shadow Copy there. Then again it did recognize it under Vista, it just didn't work. Since then Nero has released Back-It-Up & Burn, but I haven't bothered with that.
I've had no compatibility problems with any games I've tried, but then again I have only tried Fallout 3 and both Modern Warfare games. I can't imagine there would be problems with Mass Effect 2 considering Windows 7 is officially supported. I wouldn't worry about that Windows XP mode thing too much. I've never seen the need to use it for anything.
Windows 7 is pretty slick. In terms of its interface, it does share more in common with Vista than it does with XP. The taskbar is basically Microsoft's take on the Mac OS X Dock, but with a pretty cool window preview feature that I find pretty significantly increases productivity with the OS if you consistently have a lot of open windows. Also I agree with ren about the Start menu search. That's a pretty nice feature you miss if you leave Windows 7 or Vista and go back to XP. And even the side-by-side stuff they added in Vista and further expanded with the snap feature in Windows 7 is sorely missed when using XP after using one of the other two.
I use 4 GB of RAM, but then again I have for years. It runs on 4 GB just fine. 4 GB is a lot, but not overkill. With RAM being as cheap as it is now I would just go for 8 GB just so I wouldn't have to worry about it down the road.
As far as what version to get, all I can suggest is that you check out the version comparisons and see if there is a feature in one of the higher-end SKUs you care about. Personally I want stuff like domain join capability and the high end home user stuff and got Ultimate, but I have a contact at Microsoft and got it cheaply.
In the end, all I can say is that it should work fine with pretty much any modern software. I haven't seen any compatibility issues with older software, but even if you have it you can get around it by using the XP Mode (or by using something like VMware Workstation or Sun VirtualBox). The Media Center is pretty nice to if you ever decide to hook it up to a TV or combine it with an Xbox 360.