I checked out a couple of single player modifications for Half-Life 2 this morning. I highly recommend both.
MINERVA Although there's only one level at the moment, MINERVA's episodic content concept is interesting. The story is intruiging, the level is very well designed, and the action feels very much like the original game. As you progress, you receive cryptic messages from an unknown contact. Is it the G-Man? Or someone else? I guess we'll have to wait to find out.
Eclipse This might be considered to be a "total conversion" more than anything else. Eclipse is unlike Half-Life 2 in every regard. Although it's a little short, the gameplay is fun, the effects are well executed, and the artwork is incredible. The levels ran a tad slowly on my machine, but it was a fun diversion. The single weakest point in my mind is the story line; it's as thin as any I've played in a long time. But this mod has to be played to be fully appreciated. There are a few puzzles that will make you scratch your head at first, and the game makes excellent use of the Source engine's physics capabilities.
Are there any other recommended single player mods out there? If so, please let me know!