Acho que o modo mais fácil de imaginar como o entity trabalha é simplesmente esquecer que você usará a linguagem SQL - ele fará toda a escrita de código para você por baixo dos panos, e isso te permitirá a trabalhar com classes que refletem o banco de dados.
Por exemplo, vamos imaginar que você tenha uma tabela "Carro" com as colunas "Placa" e "Marca". Já que você não utilizará códigos SQL diretamente para trabalhar com essa tabela, o Entity Framework implica em você usar uma classe para isso: Você teria uma classe de nome "Carro" e duas propriedadas, uma de nome "Placa" e outra de nome "Marca". E essa informação fica dentro do contexto do Entity Framework: toda vez que você inserir um Carro dentro desse contexto, ele saberá inserir na tabela correta e com os dados corretos. Se precisar obter, você pega do contexto. Se presicar deletar, avise o contexto que esse objeto não existe mais.
Isso é uma troca: você vai trabalhar apenas com uma linguagem (compilada), então se precisar reescrecrever ou refatorar o código ou o banco de dados, torna-se um trabalho mais confiável; afinal, perde-se a necessidade de mapear manualmente objetos do banco em classes do .NET, ou fazer referências de modo não compilado. Outro ponto é que você apenas precisa definir uma estrutura inicial, tipo um setup, de como o entity irá reconhecer suas tabelas e colunas. Isso trará a você a possibilidade de fazer queries pelo código usando o "Linq", tornando mais legível para um programador back-end e mais fácil de dar manutenção por ser compilado.
Lembrando que por baixo dos panos é tudo SQL. Se você pegar a string de conexão e jogar no seu gerenciador de banco de dados, você conseguirá sem problemas, consultar os dados e alterá-los manualmente. Também consegue selecionar o tipo de banco utilizado no setup: Oracle SQL Server, MySql..., além de conseguir trocá-los or dar suporte a múltiplos bancos diferentes sem ter que mexer na estrutura de seu código ou de suas consultas. São muitas vantagens, em comparação às limitações.
As limitações são:
Performance: Um orm abstrai muitas coisas, então há um processamento mais complexo internamente do EF, tudo para deixar seu código mais simples. Se você está escrevendo um programa que precise muito de milhares de requisições em intervalos de segundos, talvez o EF não seja o melhor ORM para você. Um dapper poderia se adequar melhor.
Te deixa sem prática quando precisa escrever códigos em .SQL, ou dar manutenção em sistemas legados.
Curva de aprendizado para pessoas com pouca experiência em CodeFirst e Banco de Dados. Como tudo é invisível, o conceito de teoria dos conjuntos é quase esquecido. Isso pode dar problemas na hora de configurar como alguns relacionamentos irão se comportar