EntityFrameworkCore and get data of column which is not a KEY

Question

User1525382536 posted

how can i get a row from sql server where the row that i want to fetch

is not part of the key?

for example

public class Accounts
    {
        [Key]
        public int AccountID { get; set; }
        public string CompanyName { get; set; }
        public string Address { get; set; }
        public string Telephone { get; set; }
        public string ContactName { get; set; }
        public string CellPhone { get; set; }
        public string City { get; set; }
        public int? ZipCode { get; set; }
        public string Country { get; set; }
       
}

and i want to search it by CompanyName -how to di it?

because using

entities.AsQueryable()

and then filter it seam's a waste of resource and performance.

Answer 1

User475983607 posted

and i want to search it by CompanyName -how to di it?

The query is straight forward.

entities.Accounts.Where(m => m.CompanyName == "TheCompanyName")

and then filter it seam's a waste of resource and performance.

What are you using to measure performance?  What is your expectation?

Answer 2

User753101303 posted

Hi,

UInclear. yourDbContext.Accounts.Where(o=>o.CompanyName==value); should work. You could also have a separate Company table.

Linq to EF will just the create and run the SQL statement for you if you mean you believe it loads all data before doing the filtering on client side.

Answer 3

User1525382536 posted

i will be more clear,

i have a repository :

public class Repository<T> : IRepository<T> where T : class
    {
        private readonly ApplicationContext context;
        private DbSet<T> entities;

        public Repository(ApplicationContext context)
        {
            this.context = context;
            //set command timeout
            this.context.Database.SetCommandTimeout(new TimeSpan(0, 0, 90));
            entities = context.Set<T>();
        }
//for example
public IEnumerable<T> GetAll()
        {
            return entities.AsQueryable();
        }
}




}

and on the ConfifurseService of my API project i do 

services.AddScoped(typeof(IRepository<>), typeof(Repository<>));

and then each table ,in the ApplicationContext class, in the OnModelCreating i do :

modelBuilder.Entity<Core.DBDomain.Accounts>().ToTable("Accounts");

then in the project in every place i want to use the table i declare and inject it in the constructor :

private readonly IRepository<Accounts> _accountsRepository;

and then if want to fetch all the rows i call to

_accountsRepositoryGetAll();

now since in the class i wrote in the opening post the ID is the key,

now i want to get the row based n the CompanyName, with no change to the class or table

Answer 4

User475983607 posted

Don't use a repository.  Simply get the Accounts using Entity Framework as shown above.

Answer 5

User1525382536 posted

i have 15 tables i work with like this

what you suggest is an architecture change

i am looking for a solution within the EF

Answer 6

User475983607 posted

i have 15 tables i work with like this

what you suggest is an architecture change

i am looking for a solution within the EF

I'm a little confused.  I specifically provided an EF solution.   You are asking for a solution within your design.  Keep in mind, this is the main reason why you should NOT wrap Entity Framework in a generic repo.  It's considered an anti-pattern.

There are a few alternatives.  Adding a repository method to query by Company name.  Create a new repository to handle the new requirement.  Add a new repository method that takes a delegate filter.

https://docs.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/infrastructure-persistence-layer-implemenation-entity-framework-core

http://janholinka.net/Blog/Article/9

Answer 7

User1034446946 posted

if your accounts are directly related to a company, for database structure they should be joined by a ForeignKey,which is the primary key from the adjoining table,which would then allow you to have a generic repository, and search for accounts by company id.

looking up accounts by company name could lead to all sorts of issues, a simple one is spelling errors.

your viewmodels will just need an extra field to hold the companyId, to be stored in a hidden field in the view.

Answer 8

User1120430333 posted

I am not a fan of the generic repository pattern since EF is already using the repository pattern. So repository over the repository EF is already using is not optimal.

https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbcontext?view=efcore-3.1

<copied>

DbContext is a combination of the Unit Of Work and Repository patterns.

<end>

However, if a method is returning a collection, you can use Linq to filter the collection.

var companyobj = _accountsRepositoryGetAll().Where(a => a.CompanyName == "SomeCompany").SingleOrDefault();

if (companyobj != null)
{
     // you got a hit
}
else
{
   // you didn't get the hit
}
   

Answer 9

User1525382536 posted

DA924 - you solution is my current code and this is what i want to bypass to imrpve performance

mgebhard + how do i extend my IRepository via delegates? ok found it in your second link thanks

Answer 10

User753101303 posted

Hi,

Seems the problem is that you plan to use GetAll().AsQueryable(). What if you expose a GetByCompany method instead ?

Edit: ah based on "with no change to the class" you have no other option than to use GetAll at it seems the only retrieval method you have. Is this is real constraint or you try to always have the same interface entities regardless of what they are and what you need ?

Answer 11

User1120430333 posted

DA924 - you solution is my current code and this is what i want to bypass to imrpve performance

mgebhard + how do i extend my IRepository via delegates? ok found it in your second link thanks

The  only thing you can do then is use a stored procedure. or execute raw t-sql using EF.