The program recursively processes directories and through COM reads the specific properties of certain files. New properties can be defined by the user, and be available only for a specific file. When reading properties, I need to check if there is already such a property in the database and to add it in its absence, but how to avoid a query to the database for each comparison?

Also I can not figure out how after saving the record in Files and in Property add an entry in FileProperties?

Thank you in advance for your time and attention.

DB schema

 foreach(var path in paths){ Files file; using(var context = new DbContext()){ file = context.FirstOrDefault(f => ... ); } if(file == null){ file = new Files(){ ... }; using(var context = new DbContext()){ context.Files.Add(file); context.SaveChanges(); } var specProps = GetSpecProps(path); using(var context = new DbContext()){ foreach(var specProp in specProps){ Property prop; // отправляет запрос на сервер, а как читать из "кеша"? prop = context.Property.FirstOrDefault( p => ...); if(prop == null){ prop = new Property(){ ... }; context.Property.Add(prop); } // как здесь добавить запись в FileProperties? // System.Data.Entity.Infrastructure.DbUpdateException: // Не удалось обновить набор EntitySet "FileProperties", // поскольку в нем имеется запрос DefiningQuery и // отсутствует элемент <InsertFunction> в элементе // <ModificationFunctionMapping> для поддержки текущей операции. // context.FileProperties.Add(new FileProperties() { // File = file, // Property = prop, // property_value = specProp.Value.ToString() // }; } context.SaveChanges(); } } }
  • can try to store the table in a variable or in a class? - user2455111

1 answer 1

There are two options here.

Option one: you upload the table to the client as a whole and put it in the dictionary to quickly search it in memory. Of course, this will only work if the table is small:

 var allProps = context.Properties.ToDictionary(p => p.property_name);

Just do not, in this case, create a database context for each file, and even twice. You can write all files to the database in the same context at once. In one save operation.

Option two. It is necessary to introduce a new entity that will not have a mapping to the database. Instead, the insert operation will be mapped to a stored procedure that checks the existence of the property and creates it if necessary, after which it will return the key of the found or created property.

  • Thank you so much for your reply. Now, for "caching" I use after using dbContext.TableName.Load() , and then I execute requests so dbContext.TableName.Local.Where( ... ) . So can I? Or are there any features? - XelaNimed
  • @XelaNimed yes, this is also possible - but it will be slower (after all, linear search). - Pavel Mayorov
  • The above option with dbContext.TableName.Load() and dbContext.TableName.Local.Where( ... ) does not lead to an increase in performance and even vice versa - after reducing the table, it reduces it to several thousand entries. - XelaNimed