Hi ha moltes maneres de fer això, la normal és fer servir una collation (ordre alfabètic) diferent al de la BBDD i l'altra fent servir "[" i "]" (expressions regulars).

La collation fa que les consultes quedin algo com:

SELECT *
FROM Begudes
WHERE
   Name COLLATE Latin1_general_CI_AI Like '%cafe%' COLLATE Latin1_general_CI_AI

Això trobarà "cafè", "café"... Però a mi no m'agrada gaire. Prefereixo un altra mètode.

Es tracta de substituir totes les vocals de la cerca per [aàáAÀÁ], [eèéEÈÉ]... quedaria:

SELECT *
FROM Begudes
WHERE
   Name Like '%c[aàáAÀÁ]f[eèéEÈÉ]%'

No he trobat dades de rendiment ni recomanacions, tampoc en els llocs on ho he necessitat he vist que funcionés malament. El que sí he necessitat és una funció que em substitueixi les vocals per l'expressió regular. L'he afegit a la clsDades.

/// <summary>
/// Donat un string que formarà part d'un LIKE el torna ACCENT INSENSITIVE
/// </summary>
/// <param name="Valor">L'string a convertir</param>
/// <returns>L'string convertit</returns>
private string FerAccentInsesitive(string Valor)
{
    string Result = Valor;
    string a = "[aàáAÀÁ]";
    string e = "[eèéEÈÉ]";
    string i = "[iìíIÌÍïÏ]";
    string o = "[oòóOÒÓ]";
    string u = "[uùúUÙÚüÜ]";

    Result = Result.Replace("a", "_a_");
    Result = Result.Replace("e", "_e_");
    Result = Result.Replace("i", "_i_");
    Result = Result.Replace("o", "_o_");
    Result = Result.Replace("u", "_u_");

    Result = Result.Replace("à", "_à_");
    Result = Result.Replace("è", "_è_");
    Result = Result.Replace("ì", "_ì_");
    Result = Result.Replace("ò", "_ò_");
    Result = Result.Replace("ù", "_ù_");

    Result = Result.Replace("á", "_á_");
    Result = Result.Replace("é", "_é_");
    Result = Result.Replace("í", "_í_");
    Result = Result.Replace("ó", "_ó_");
    Result = Result.Replace("ú", "_ú_");

    Result = Result.Replace("A", "_A_");
    Result = Result.Replace("E", "_E_");
    Result = Result.Replace("I", "_I_");
    Result = Result.Replace("O", "_O_");
    Result = Result.Replace("U", "_U_");

    Result = Result.Replace("À", "_À_");
    Result = Result.Replace("È", "_È_");
    Result = Result.Replace("Ì", "_Ì_");
    Result = Result.Replace("Ò", "_Ò_");
    Result = Result.Replace("Ù", "_Ù_");

    Result = Result.Replace("Á", "_Á_");
    Result = Result.Replace("É", "_É_");
    Result = Result.Replace("Í", "_Í_");
    Result = Result.Replace("Ó", "_Ó_");
    Result = Result.Replace("Ú", "_Ú_");

    //----------------------------------------

    Result = Result.Replace("_a_", a);
    Result = Result.Replace("_e_", e);
    Result = Result.Replace("_i_", i);
    Result = Result.Replace("_o_", o);
    Result = Result.Replace("_u_", u);

    Result = Result.Replace("_à_", a);
    Result = Result.Replace("_è_", e);
    Result = Result.Replace("_ì_", i);
    Result = Result.Replace("_ò_", o);
    Result = Result.Replace("_ù_", u);

    Result = Result.Replace("_á_", a);
    Result = Result.Replace("_é_", e);
    Result = Result.Replace("_í_", i);
    Result = Result.Replace("_ó_", o);
    Result = Result.Replace("_ú_", u);

    Result = Result.Replace("_A_", a);
    Result = Result.Replace("_E_", e);
    Result = Result.Replace("_I_", i);
    Result = Result.Replace("_O_", o);
    Result = Result.Replace("_U_", u);

    Result = Result.Replace("_À_", a);
    Result = Result.Replace("_È_", e);
    Result = Result.Replace("_Ì_", i);
    Result = Result.Replace("_Ò_", o);
    Result = Result.Replace("_Ù_", u);

    Result = Result.Replace("_Á_", a);
    Result = Result.Replace("_É_", e);
    Result = Result.Replace("_Í_", i);
    Result = Result.Replace("_Ó_", o);
    Result = Result.Replace("_Ú_", u);

    return Result;
}