Carderro's is on the waterfront, and is usually quite good. It's slightly overpriced and leans from restaurant towards lounge atmosphere on weekend evenings after 8 pm.
http://www.vancouverdine.com/carderos All of The Boathouse restaurants is a local chain that serves great food. The location in Horseshoe Bay has the best scenery, but you need a car (or a lot of cab fare) to get there.
http://boathouserestaurants.ca/ I also love Vancouver's Chinese restaurants, many of which actually serve real Chinese cuisine, not deep-fried-and-covered-in-sweet-sauce excuses for Chinese food found elsewhere in North America. My favorite appears to be closed now - it was a Coquitlam location of the Kirin fine dining chain. Their other restaurants remain open and are likely good quality.
http://www.kirinrestaurants.com/ Other dining experiences that are not fine dining, but are unique and worth checking out are: Japa Dog - Japanese style hot dogs. Don't knock it until you've tried it.
http://www.japadog.com/ Fish and chips: My favorite was Mr. Pickwick's on Denman street (not far from Stanley Park), but C-Lovers and Pajo's are all good too. The busier the location, the more fresh your fish will be. Sushi: You can't walk two blocks in Vancouver without passing several sushi restaurants. They often serve a mix of traditional Japanese-style sushi, California-style sushi, and the occasional "what were they thinking?!?!" creations (like bacon avocado rolls). A local specialty is the BC roll: cooked salmon (with the crispy skin). Delicious. A few all-you-can eat restaurants are dotted around the city, plus some table-service restaurants, and a huge number of counter service restaurants.