But he's stubborn and calling it performance overhead which is yet beyond my understanding
It's hard to have a detailed discussion about something when you don't understand (and thus are unable to acknowledge) part of the problema outset.
Very simply put: automation can only be so clever. When dealing with complex and intricate queries, EF will generate a working query, but it won't be as efficient as you want it to be.
EF isn't badly developed. The generated inefficient queries are just an inevitable consequence of what EF is (and isn't) built to do. EF is a great tool that avoid trivial and repetitive code; but those benefits are lost when you're dealing with highly customized (generally complex) queries.
Think of it this way: my sedan is built for road use. On the road, it will be much easier to drive than a large 4x4. Can I take my car offroad? Within reason, yes. But my car wasn't built for offroading, and thus my car's benefits are lost when not on the road, whereas the 4x4 starts to shine and will be easier to drive offroad.
Like my car is built for road use, EF is built for simple CRUD tasks. It can be used in other ways but no one every designed it with that in mind.
"Look, the simple thing is that you can use both but what's the point of using an ORM if you are doing it all in database i.e. stored procedures."
That argument makes little sense. What's the point of using a toothbrush if your arm moves the toothbrush anyway. Just use your arm to clean your teeth!
Well, this isn't the case, I have seen blogs and examples where people are doing massive things with EF and didn't need to write any procedure.
Just because it can be done does not mean that it is being done well. I've seen people eat the food I made but that doesn't mean I'm a good cook. It just means I'm not fully incapable of cooking.
EF can do many things, but its efficiency starts falling apart as the complexity of the query increases. In cases where the subsequent performance hit is unacceptable, EF is not the tool to use.
So, my question is that whether you only need to handle CRUD in ef or should do a lot more in EF as a replacement of stored procedures.
Like many things, it depends on what you need.
For simple CRUD operations, EF is a good tool to use. It removes the need for a lot of boilerplated handling logic.
If you only have simple data queries, or don't care about optimizing performance, then you can use EF for those since you're already using EF for CRUD anyway.
However, if you have intricate data queries and care about the performance optimization, then you won't want to use EF. Our lead architect (who used to be a DBA and very much cares about DB performance) currently advocates for using EF and Dapper simultaneously, specifically because they each specialize in part of the workload. He initially suggested only using Dapper, but he has conceded the ease of CRUD with EF and accepts its usage in CRUD-heavy applications.
But again this is subjective. You might prefer to stick to one tool and suck up the drawbacks (e.g. use EF and deal with slow complex queries; or use Dapper and deal with having to write boilerplate CRUD code). Or you may be in favor of using different tools so that you can squeeze performance at all times.