I lived just outside the City proper and loved it (maybe six years ago), actually very close to Liverpool Street. The City is a very special place and definitely has more to it than its offices, so I can see the appeal!
A few weekends ago I happened to walk through the Square Mile and it was definitely still empty. From a practical perspective, it never bothered me too much as I was close to the action in Shoreditch and could also walk to the West End (so you'll definitely get your culture fill). I imagine it'd be even easier these days with so many instant grocery delivery services available in London. One thing I missed was my gym, which was only open for a few hours on Saturday and closed on Sunday. Otherwise the emptiness can be a nice change of pace and a good opportunity to really explore the architecture and history.
However, the thing that always bothered me (and still did when I went a few weeks ago) is that to me, it feels really eerie on the weekends - a bit like a ghost town or sci fi movie. I've worked in the City and in Canary Wharf, and have on occasion spent a Saturday or Sunday in either. For some reason the City always feels a bit "wrong" to me outside of Mon-Fri - even Canary Wharf feels more "human," maybe because of the weekend shoppers? I can't put my finger on why the weekend City gives me the heebie jeebies, but it does...
I'd also say that foot traffic is a lot worse in the City than Canary Wharf. In CW I find that I can usually walk at a brisk pace even at peak times, whereas the City gets more congested due to narrower streets etc. even after COVID. Towards Liverpool Street it's not so bad, but as soon as I get closer to the Bank of England area I rarely beat the Google Maps estimate and am sometimes late.
Of my friends who lived within the Square Mile, virtually all were fresh transplants who had moved to London for work. Many of them preferred being walking distance from the office in case they had to come in on short notice on weekends. Most of them left within a year or two - I think they found that none of their friends wanted to come see them in the City after they'd spent all week working there. And they themselves often weren't too keen on being so close to their office after some time... could it be a bit too close for comfort for your DH? And/or if your friends also work in the City, they might not be as keen to venture in yet again for a dinner party at yours over the weekend? On the flip side, however, it will become VERY easy to organise drinks and dinner on a weeknight!
I hear completely different things about the Barbican complex, though (much in line with what's been said above, both good and bad).
To end on a positive note, I kept on bumping into people from my wider industry, and I mean constantly. I actually really enjoyed this when I just started working in London. I am a transplant myself and it was a good way to keep in touch - it gives a corporate neighbourhood this strangely uplifting village feel at times. That said, it's maybe not so good if you're having an off day ;)